using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using Newtonsoft.Json;
using Nop.Core;
using Nop.Core.Caching;
using Nop.Core.Data;
using Nop.Core.Domain.Common;
using Nop.Core.Domain.Customers;
using Nop.Core.Domain.Directory;
using Nop.Core.Domain.Logistics;
using Nop.Core.Domain.Orders;
using Nop.Core.Domain.Shipping;
using Nop.Services.Common;
using Nop.Services.Customers;
using Nop.Services.Helpers;
using Nop.Services.Localization;
using Nop.Services.Logging;
using Nop.Services.Orders;
using Nop.Services.Shipping;
using Nop.Services.Shipping.Tracking;
using StackExchange.Profiling;


namespace Nop.Services.Logistics
{
    public partial class LogisticsService : ILogisticsService
    {
        #region Constants

        private const string LOGISTICSZONES_ALLZONES_BY_COUNTRYCODE_KEY = "Spotshub.logisticszones.allzones.countrycode-{0}";
        private const string LOGISTICSWAREHOUSES_BY_REGIONID_KEY = "Spotshub.logisticswarehouses.region-{0}";
        private const string LOGISTICSCOURIERS_BY_DISTRICTID_KEY = "Spotshub.logisticscouriers.district-{0}";
        private const string LOGISTICSCOURIERS_PATTERN_KEY = "Spotshub.logisticscouriers.";

        #endregion

        private readonly IRepository<LogisticsCourier> _courierRepository;
        private readonly IRepository<LogisticsLocation> _locationRepository;
        private readonly IRepository<LogisticsPackage> _packageRepository;
        private readonly IRepository<LogisticsPlan> _planRepository;
        private readonly IRepository<LogisticsWarehouse> _warehouseRepository;
        private readonly IRepository<ShippingTimeWindow> _shippingTimeWindowRepository;
        private readonly IRepository<Region> _regionRepository;
        private readonly IRepository<District> _districtRepository;
        private readonly IRepository<Zone> _zoneRepository;
        private readonly IRepository<ZoneGroup> _zoneGroupRepository;
        private readonly IRepository<PlanNote> _planNoteRepository;
        private readonly IRepository<PackageNote> _packageNoteRepository;
        private readonly IRepository<Address> _addressRepository;
        private readonly IRepository<PackageNoteTemplate> _packageNoteTemplateRepository;
        private readonly IRepository<CustomerRole> _customerRole;
        private readonly IAddressService _addressSerivce;
        private readonly IShipmentService _shipmentService;
        private readonly ICustomerService _customerService;
        private readonly IOrderService _orderService;
        private readonly IDateTimeHelper _dateTimeHelper;
        private readonly IWorkContext _workContext;
        private readonly ICacheManager _cacheManager;
        private readonly LogisticsInformationSettings _logisticsInformationSettings;
        private readonly ILocalizationService _localizationService;
        private readonly IShipingDayService _shipingDayService;
        private readonly IRepository<Order> _ordersRepository;
        private readonly IRepository<Shipment> _shipmentRepository;
        private readonly IGenericAttributeService _genericAttributeService;
        private readonly ILogger _logger;

        public LogisticsService(
            IRepository<LogisticsCourier> courierRepository,
            IRepository<LogisticsLocation> locationRepository,
            IRepository<LogisticsPackage> packageRepository,
            IRepository<LogisticsPlan> planRepository,
            IRepository<LogisticsWarehouse> warehouseRepository,
            IRepository<Region> regionRepository,
            IRepository<District> districtRepository,
            IRepository<Zone> zoneRepository,
            IRepository<ZoneGroup> zoneGroupRepository,
            IRepository<PlanNote> planNoteRepository,
            IRepository<PackageNote> packageNoteRepository,
            IRepository<ShippingTimeWindow> shippingTimeWindowRepository,
            IRepository<Address> addressRepository,
            IRepository<PackageNoteTemplate> packageNoteTemplateRepository,
            IRepository<CustomerRole> customerRole,
            IRepository<Order> ordersRepository,
            IRepository<Shipment> shipmentRepository,
            IAddressService addressService,
            IShipmentService shipmentService,
            ICustomerService customerService,
            IOrderService orderSerivce,
            IDateTimeHelper dateTimeHelper,
            IWorkContext workContext,
            ICacheManager cacheManager,
            ILogger logger,
            LogisticsInformationSettings logisticsInformationSettings,
            ILocalizationService localizationService,
            IShipingDayService shipingDayService,
            IGenericAttributeService genericAttributeService
            )
        {
            this._courierRepository = courierRepository;
            this._locationRepository = locationRepository;
            this._packageRepository = packageRepository;
            this._planRepository = planRepository;
            this._warehouseRepository = warehouseRepository;
            this._regionRepository = regionRepository;
            this._districtRepository = districtRepository;
            this._zoneRepository = zoneRepository;
            this._zoneGroupRepository = zoneGroupRepository;
            this._planNoteRepository = planNoteRepository;
            this._packageNoteRepository = packageNoteRepository;
            this._shippingTimeWindowRepository = shippingTimeWindowRepository;
            this._addressRepository = addressRepository;
            this._packageNoteTemplateRepository = packageNoteTemplateRepository;
            this._customerRole = customerRole;
            this._addressSerivce = addressService;
            this._shipmentService = shipmentService;
            this._customerService = customerService;
            this._orderService = orderSerivce;
            this._dateTimeHelper = dateTimeHelper;
            this._workContext = workContext;
            this._cacheManager = cacheManager;
            this._logger = logger;
            this._logisticsInformationSettings = logisticsInformationSettings;
            this._localizationService = localizationService;
            this._shipingDayService = shipingDayService;
            this._ordersRepository = ordersRepository;
            this._shipmentRepository = shipmentRepository;
            this._genericAttributeService = genericAttributeService;
        }

        #region Logistics Courier

        public void InsertCourier(Customer customer, int[] districtIds)
        {
            if (customer == null)
                throw new ArgumentNullException("customer");

            if (districtIds == null || districtIds.Length == 0)
            {
                throw new ArgumentException("The district IDs must be specified.", "districtIds");
            }

            var courier = new LogisticsCourier()
            {
                Id = customer.Id
            };

            courier.DistrictIds = districtIds;

            _courierRepository.Insert(courier);

            _cacheManager.RemoveByPattern(LOGISTICSCOURIERS_PATTERN_KEY);
        }

        public void UpdateCourier(LogisticsCourier courier)
        {
            if (courier == null)
                throw new ArgumentNullException("courier");

            _courierRepository.Update(courier);

            _cacheManager.RemoveByPattern(LOGISTICSCOURIERS_PATTERN_KEY);
        }

        public void DeleteCourier(LogisticsCourier courier)
        {
            if (courier == null)
                throw new ArgumentNullException("courier");

            _courierRepository.Delete(courier);

            _cacheManager.RemoveByPattern(LOGISTICSCOURIERS_PATTERN_KEY);
        }

        public LogisticsCourier GetCourierById(int courierId)
        {
            return _courierRepository.GetById(courierId);
        }

        public List<LogisticsCourier> GetPlannedCouriers(District district = null, PlanStatus planStatus = PlanStatus.Locked)
        {
            var query = from p in _planRepository.Table
                        select p;
            if (district != null)
            {
                query = from p in query
                        where p.DistrictId == district.Id
                        select p;
            }

            var query2 = from p in query
                         where p.Status == planStatus
                         select p.Courier;
            return query2.ToList();
        }

        public List<LogisticsCourier> GetCouriers(District district)
        {
            string key = String.Format(LOGISTICSCOURIERS_BY_DISTRICTID_KEY, district == null ? 0 : district.Id);
            return _cacheManager.Get(key, () =>
            {
                var query = from c in _courierRepository.Table
                            where !c.Customer.Deleted &&
                                c.Customer.Active &&
                                c.Customer.CustomerRoleMappings.Any(cr => cr.CustomerRole.SystemName == SystemCustomerRoleNames.Courier &&
                                cr.CustomerRole.Active)
                            select c;

                if (district == null)
                {
                    return query.ToList();
                }

                return query.ToList().Where(x => x.DistrictIds.Contains(district.Id)).ToList();
            });
        }

        #endregion

        #region Logistics Package

        public IPagedList<LogisticsPackage> GetPendingPlanPackages(ShippingStatus[] shippingStatuses, District district, int[] timeSpans, bool includeMaxDeliveryAttempt = true, LogisticsWarehouse warehouse = null, int pageIndex = 0, int pageSize = int.MaxValue)
        {
            // TODO: Improve performance
            var districtId = district == null ? 0 : district.Id;
            var query = from p in _packageRepository.Table
                        select p;

            //Exclude delete order
            ExcludeDeletedOrderQueryable(query);

            if (timeSpans != null && timeSpans.Length > 0)
            {
                query = TimeSpanQueryable(query, timeSpans);
            }

            if (districtId != 0)
            {
                query = from p in query
                        where
                            // In the warehouse of the district
                            ((p.Status == ShippingStatus.InWarehouse || p.Status == ShippingStatus.InWarehouseFailedDelivery) && p.WarehouseId.HasValue && p.Warehouse.DistrictId == districtId) ||
                            // Not picked up in the district
                            ((p.Status == ShippingStatus.ReadyForPickUp || p.Status == ShippingStatus.NotYetReady) && p.PickUpLocation.DistrictId == districtId)
                        select p;
            }

            if (warehouse != null)
            {
                // Of packages pre-selected above, include:
                // - those that have not yet been picked up
                // - those in warehouse that matches the specified warehouse.
                query = from p in query
                        where
                            ((p.Status == ShippingStatus.InWarehouse || p.Status == ShippingStatus.InWarehouseFailedDelivery) && p.WarehouseId.HasValue && (p.WarehouseId == warehouse.Id)) ||
                            (p.Status == ShippingStatus.ReadyForPickUp || p.Status == ShippingStatus.NotYetReady)
                        select p;
            }

            if (shippingStatuses != null && shippingStatuses.Count() > 0)
            {
                query = from p in query
                        where (shippingStatuses.Contains(p.Status))
                        select p;
            }

            if (!includeMaxDeliveryAttempt)
            {
                query = from p in query
                        where !(// exclude followings
                                p.Status == ShippingStatus.InWarehouseFailedDelivery && p.DeliveryAttemptCount != null &&
                                (
                                    (p.DeliveryAttemptCount >= MaxDeliveryAttemptCount) ||
                                    (p.DeliveryAttemptCount == 1 && p.DeliveryLocation.Address.TypeId == (int)AddressType.Residential && (p.Shipment.Attributes.HasValue && ((AddtionalAttributes)p.Shipment.Attributes & AddtionalAttributes.NoSignature) == AddtionalAttributes.NoSignature))
                                )
                        )
                        select p;
            }

            // Of packages pre-selected above, make sure they are not in plans or are in completed plans
            query = from p in query
                    where ((p.Status == ShippingStatus.InWarehouse || p.Status == ShippingStatus.InWarehouseFailedDelivery) &&
                            (!p.DeliveryLocation.PlanId.HasValue || p.DeliveryLocation.Plan.Status == PlanStatus.Closed) &&
                            (!p.PickUpLocation.PlanId.HasValue || p.PickUpLocation.Plan.Status == PlanStatus.Closed)) ||
                        ((p.Status == ShippingStatus.ReadyForPickUp || p.Status == ShippingStatus.NotYetReady) &&
                        (!p.PickUpLocation.PlanId.HasValue || p.PickUpLocation.Plan.Status == PlanStatus.Closed))
                    select p;
            query = query.OrderBy(p => p.Status).ThenByDescending(p => p.CreatedOnUtc);
            return new PagedList<LogisticsPackage>(query, pageIndex, pageSize);
        }

        private IQueryable<LogisticsPackage> TimeSpanQueryable(IQueryable<LogisticsPackage> query, int[] timeSpans)
        {
            if (timeSpans.Contains(0))
            {
                query = from pk in query
                        join sh in _shipmentRepository.Table on pk.ShipmentId equals sh.Id
                        join ord in _ordersRepository.Table on sh.OrderId equals ord.Id
                        join eord in _ordersRepository.Table on ord.BoundOrderId equals eord.Id
                        where ord.BoundOrderId != null && (!eord.ShippingTimeWindowsId.HasValue || (eord.ShippingTimeWindowsId.HasValue && timeSpans.Contains(eord.ShippingTimeWindowsId.Value))) && !ord.Deleted
                        select pk;
                var queryLogistics = from pk in _packageRepository.Table
                                     join sh in _shipmentRepository.Table on pk.ShipmentId equals sh.Id
                                     join ord in _ordersRepository.Table on sh.OrderId equals ord.Id
                                     where ord.BoundOrderId == null && (!ord.ShippingTimeWindowsId.HasValue || (ord.ShippingTimeWindowsId.HasValue && timeSpans.Contains(ord.ShippingTimeWindowsId.Value))) && !ord.Deleted
                                     select pk;
                return query.Union(queryLogistics);
            }
            else
            {
                query = from pk in query
                        join sh in _shipmentRepository.Table on pk.ShipmentId equals sh.Id
                        join ord in _ordersRepository.Table on sh.OrderId equals ord.Id
                        join eord in _ordersRepository.Table on ord.BoundOrderId equals eord.Id
                        where ord.BoundOrderId != null && eord.ShippingTimeWindowsId.HasValue && timeSpans.Contains(eord.ShippingTimeWindowsId.Value) && !ord.Deleted
                        select pk;

                var queryLogistics = from pk in _packageRepository.Table
                                 join sh in _shipmentRepository.Table on pk.ShipmentId equals sh.Id
                                 join ord in _ordersRepository.Table on sh.OrderId equals ord.Id
                                 where ord.BoundOrderId == null && ord.ShippingTimeWindowsId.HasValue && timeSpans.Contains(ord.ShippingTimeWindowsId.Value) && !ord.Deleted
                                 select pk;
                return query.Union(queryLogistics);
            }
        }

        private IQueryable<LogisticsPackage> ExcludeDeletedOrderQueryable(IQueryable<LogisticsPackage> query)
        {
            query = from pk in query
                    join sh in _shipmentRepository.Table on pk.ShipmentId equals sh.Id
                    join ord in _ordersRepository.Table on sh.OrderId equals ord.Id
                    join eord in _ordersRepository.Table on ord.BoundOrderId equals eord.Id
                    where !ord.Deleted || !eord.Deleted
                    select pk;

            return query;
        }

        public IPagedList<LogisticsPackage> GetPendingPlanPackagesByZone(int[] zoneIds, int[] orderKind, ShippingStatus[] shippingStatuses, District district, int[] timeSpans, bool includeMaxDeliveryAttempt = false, LogisticsWarehouse warehouse = null, int pageIndex = 0, int pageSize = int.MaxValue)
        {
            // Process failed in hub packages to make sure the packages have delivery count information.
            if (!includeMaxDeliveryAttempt)
            {
                _packageRepository.Table
                    .Where(p => p.Status == ShippingStatus.InWarehouseFailedDelivery && p.DeliveryAttemptCount == null && p.DeliveryLocation.PlanId == null && p.WarehouseId.HasValue)
                    .ToList()
                    .ForEach(p => GetFailedDeliveryEventCount(p));
            }

            if (zoneIds == null || zoneIds.Length == 0)
                return GetPendingPlanPackages(shippingStatuses, district, timeSpans, includeMaxDeliveryAttempt, warehouse, pageIndex, pageSize);
            else
            {
                var zipPostalCodes = zoneIds.Select(x => _zoneRepository.GetById(x))
                    .SelectMany(x => x.GetZipPostalCodeArray());

                var query = from p in _packageRepository.Table
                            select p;

                //Exclude delete order
                ExcludeDeletedOrderQueryable(query);

                if (district != null)
                {
                    query = from p in query
                            where
                                // In the warehouse of the district
                                ((p.Status == ShippingStatus.InWarehouse || p.Status == ShippingStatus.InWarehouseFailedDelivery) && p.WarehouseId.HasValue && p.Warehouse.DistrictId == district.Id) ||
                                // Not picked up in the district
                                ((p.Status == ShippingStatus.ReadyForPickUp || p.Status == ShippingStatus.NotYetReady) && p.PickUpLocation.DistrictId == district.Id)
                            select p;
                }

                if (timeSpans != null && timeSpans.Length > 0)
                {
                    query = TimeSpanQueryable(query, timeSpans);
                }

                if (warehouse != null)
                {
                    query = from p in query
                            where
                                ((p.Status == ShippingStatus.InWarehouse || p.Status == ShippingStatus.InWarehouseFailedDelivery) && p.WarehouseId.HasValue && (p.WarehouseId == warehouse.Id)) ||
                                (p.Status == ShippingStatus.ReadyForPickUp || p.Status == ShippingStatus.NotYetReady)
                            select p;
                }

                if (shippingStatuses != null && shippingStatuses.Count() > 0)
                {
                    query = from p in query
                            where (shippingStatuses.Contains(p.Status))
                            select p;
                }

                // exclude the max delivery packages
                if (!includeMaxDeliveryAttempt)
                {
                    query = from p in query
                            where !(// exclude followings
                                    p.Status == ShippingStatus.InWarehouseFailedDelivery && p.DeliveryAttemptCount != null &&
                                    (
                                        (p.DeliveryAttemptCount >= MaxDeliveryAttemptCount) ||
                                        (p.DeliveryAttemptCount == 1 && p.DeliveryLocation.Address.TypeId == (int)AddressType.Residential && (p.Shipment.Attributes.HasValue && ((AddtionalAttributes)p.Shipment.Attributes & AddtionalAttributes.NoSignature) == AddtionalAttributes.NoSignature))
                                    )
                            )
                            select p;
                }

                //TODO: Should only work for Canada postal codes now.
                query = from p in query
                        where ((p.Status == ShippingStatus.InWarehouse || p.Status == ShippingStatus.InWarehouseFailedDelivery) &&
                                (!p.DeliveryLocation.PlanId.HasValue || p.DeliveryLocation.Plan.Status == PlanStatus.Closed) &&
                                (!p.PickUpLocation.PlanId.HasValue || p.PickUpLocation.Plan.Status == PlanStatus.Closed) &&
                                zipPostalCodes.Any(x => p.DeliveryLocation.Address.ZipPostalCode.Substring(0, 3) == x)) ||
                                ((p.Status == ShippingStatus.ReadyForPickUp || p.Status == ShippingStatus.NotYetReady) &&
                                (!p.PickUpLocation.PlanId.HasValue || p.PickUpLocation.Plan.Status == PlanStatus.Closed) &&
                                zipPostalCodes.Any(x => p.PickUpLocation.Address.ZipPostalCode.Substring(0, 3) == x))
                        select p;

                if (orderKind != null && orderKind.Count() != 0)
                {
                    query = from p in query
                            where orderKind.Contains(p.OrderKindId)
                            select p;
                }

                query = query.OrderBy(p => p.Status).ThenBy(p => p.CreatedOnUtc);

                return new PagedList<LogisticsPackage>(query, pageIndex, pageSize);
            }
        }

        public LogisticsPackage GetPackageById(int id)
        {
            return _packageRepository.GetById(id);
        }

        public LogisticsPackage GetPackageByShipmentId(int shipmentId)
        {
            var query = from p in _packageRepository.Table
                        where p.ShipmentId == shipmentId
                        select p;
            return query.FirstOrDefault();
        }

        public List<LogisticsPackage> GetPackagesByShipmentIds(int[] shipmentIds)
        {
            var query = from p in _packageRepository.Table
                        where shipmentIds.Contains(p.ShipmentId)
                        select p;
            return query.ToList();
        }

        public List<LogisticsPackage> GetPackagesByPickupLocation(int planId, LogisticsLocation location, bool countRemainingOnly = false)
        {
            if (location == null)
                throw new ArgumentNullException("location");

            if (planId != location.PlanId)
                throw new Exception("planId parameter and location's planId are different.");

            var addressId = location.AddressId;

            var query = from pk in _packageRepository.Table
                        where (pk.PickUpLocation.PlanId.HasValue &&
                        pk.PickUpLocation.PlanId.Value == planId) &&
                        (pk.PickUpLocation.AddressId == addressId)
                        select pk;

            var pkgList = query.ToList();

            if (countRemainingOnly)
            {
                pkgList = pkgList.Where(p => PackageAtPickupLocation(p) && !PackagePickupAttempted(p)).ToList();
            }

            return pkgList;
        }

        public List<LogisticsPackage> GetPackagesByDeliveryLocation(int planId, LogisticsLocation location, bool countRemainingOnly = false)
        {
            if (location == null)
                throw new ArgumentNullException("location");

            if (planId != location.PlanId)
                throw new Exception("planId parameter and location's planId are different.");

            var addressId = location.AddressId;

            // must exclude packages that, although have the same final delivery location address, but
            // are now actually en-route to a warehouse.
            var query = from pk in _packageRepository.Table
                        where (pk.DeliveryLocation.PlanId.HasValue &&
                        pk.DeliveryLocation.PlanId.Value == planId) &&
                        !pk.ToWarehouseId.HasValue &&
                        (pk.DeliveryLocation.AddressId == addressId)
                        select pk;

            var pkgList = query.ToList();

            if (countRemainingOnly)
            {
                pkgList = pkgList.Where(p => (p.Status != ShippingStatus.FailedPickUp) &&
                                             !PackageDeliveryAttempted(p)).ToList();
            }

            return pkgList;
        }

        public List<LogisticsPackage> GetPackagesToWarehouse(LogisticsPlan plan, LogisticsWarehouse warehouse)
        {
            if (warehouse == null)
                throw new ArgumentNullException("warehouse");

            if (plan == null)
                throw new ArgumentNullException("plan");

            var query = from pk in _packageRepository.Table
                        where (pk.DeliveryLocation.PlanId.HasValue &&
                        pk.DeliveryLocation.PlanId.Value == plan.Id) &&
                        (pk.ToWarehouseId.HasValue && pk.ToWarehouseId.Value == warehouse.Id)
                        select pk;

            var pkgList = query.ToList();

            return pkgList;
        }

        public List<LogisticsPackage> GetPackagesByPlanId(int planId)
        {
            var query = from pk in _packageRepository.Table
                        where (pk.DeliveryLocation.PlanId.HasValue && pk.DeliveryLocation.PlanId == planId) ||
                        (pk.PickUpLocation.PlanId.HasValue && pk.PickUpLocation.PlanId == planId)
                        select pk;
            return query.ToList();
        }

        public LogisticsPackage GetPlanNextPackage(int planId, int currentPackageId)
        {
            // figure out the current rank based on the current package.
            // Will use this rank to decide what is the next package later.
            var currPkg = GetPackageById(currentPackageId);

            int currRank = 0;

            if (((currPkg.Status == ShippingStatus.InWarehouse || currPkg.Status == ShippingStatus.InWarehouseFailedDelivery) &&
                 currPkg.DeliveryLocation.PlanId.HasValue && currPkg.DeliveryLocation.PlanId.Value == planId) ||
                currPkg.Status == ShippingStatus.Delivering ||
                currPkg.Status == ShippingStatus.Delivered)
            {
                currRank = currPkg.DeliveryLocation.Rank ?? -1;
            }
            else if (currPkg.Status == ShippingStatus.ReadyForPickUp ||
                currPkg.Status == ShippingStatus.FailedPickUp ||
                currPkg.Status == ShippingStatus.ReturningWarehouse ||
                (currPkg.Status == ShippingStatus.InWarehouse && currPkg.PickUpLocation.PlanId.HasValue &&
                 currPkg.PickUpLocation.PlanId.Value == planId))
            {
                currRank = currPkg.PickUpLocation.Rank ?? -1;
            }


            // Technically should not hit this. But, just in case, we throw errors!
            if (currRank == -1)
            {
                throw new Exception("Cannot have both Delivery and PickUp Locations not ranked if package is in a routing plan! Package ID = "
                    + currentPackageId.ToString() + " PlanID = " + planId.ToString());
            }

            // Retrieve only those packages whose ranks (either pickup or delivery) are larger
            // NOTE: smaller rank numbers are ranked higher in order of service, i.e.: smaller rank numbers
            //       are serviced before larger rank numbers.

            var query = from pk in _packageRepository.Table
                        where ((pk.DeliveryLocation.PlanId.HasValue && pk.DeliveryLocation.PlanId == planId &&
                                pk.DeliveryLocation.Rank.HasValue && pk.DeliveryLocation.Rank > currRank) ||
                               (pk.PickUpLocation.PlanId.HasValue && pk.PickUpLocation.PlanId == planId &&
                                pk.PickUpLocation.Rank.HasValue && pk.PickUpLocation.Rank > currRank))
                        select pk;

            if (query.Count() == 0)
            {
                // no more package "next" in the plan.
                return null;
            }

            // Now extract the addresses from these packages and sort via ranks!
            var pickupLocQuery = from pk in query
                                 where pk.PickUpLocation.PlanId.HasValue && pk.PickUpLocation.PlanId == planId &&
                                       pk.PickUpLocation.Rank.HasValue && pk.PickUpLocation.Rank > currRank
                                 select pk.PickUpLocation;

            var deliveryLocQuery = from pk in query
                                   where pk.DeliveryLocation.PlanId.HasValue && pk.DeliveryLocation.PlanId == planId &&
                                         pk.DeliveryLocation.Rank.HasValue && pk.DeliveryLocation.Rank > currRank
                                   select pk.DeliveryLocation;

            var rankedLocations = pickupLocQuery.ToList();
            rankedLocations.AddRange(deliveryLocQuery.ToList());

            var nextLocation = rankedLocations.Distinct().OrderBy(x => x.Rank.Value).FirstOrDefault();
            var nextPackage = nextLocation.Package;

            return nextPackage;
        }

        public List<LogisticsPackage> GetPlanSkipPackages(int planId)
        {
            // Method no longer useable, since will not have skipped packages.
            // So, returning empty list all the time.
            return new List<LogisticsPackage>();

            /*
            var query = from pk in _packageRepository.Table
                        where ((pk.DeliveryLocation.PlanId.HasValue && pk.DeliveryLocation.PlanId == planId) ||
                        (pk.PickUpLocation.PlanId.HasValue && pk.PickUpLocation.PlanId == planId)) &&
                        (pk.Status == ShippingStatus.SkipPickUp || pk.Status == ShippingStatus.SkipDelivering)
                        select pk;

            // Now extract the addresses from these packages and sort via ranks!
            var pickupLocQuery = from pk in query
                                 select pk.PickUpLocation;

            var deliveryLocQuery = from pk in query
                                   select pk.DeliveryLocation;

            var rankedLocations = pickupLocQuery.ToList();
            rankedLocations.AddRange(deliveryLocQuery.ToList());

            var pkgList = rankedLocations.Distinct().OrderByDescending(x => x.Rank.Value).Select(x => x.Package).Distinct().ToList();
            return pkgList;
            */
        }

        public virtual IPagedList<LogisticsPackage> SearchPackagesByCustomerId(
            int CustomerId,
            DateTime? startTime,
            DateTime? endTime,
            ShippingStatus[] shippingStatuses,
            string postalCode,
            string trackingNumber,
            int? districtId,
            int? pickupDistrictId,
            int? deliveryDistrictId,
            Zone pickupZone,
            Zone deliveryZone,
            AddressType? pickupAddressType,
            AddressType? deliveryAddressType,
            int[] OrderKindId,
            int pageIndex, int pageSize)
        {
            //shopping order shipment 
            var query = from pk in _packageRepository.Table
                        select pk;
            //shipping order shipment
            var query2 = from pk in _packageRepository.Table
                         select pk;
            if (CustomerId != 0)
            {
                query = from pk in query

                        join sh in _shipmentRepository.Table on pk.ShipmentId equals sh.Id
                        join ord in _ordersRepository.Table on sh.OrderId equals ord.Id
                        join eord in _ordersRepository.Table on ord.BoundOrderId equals eord.Id
                        where (eord.CustomerId == CustomerId) && ord.BoundOrderId != null
                        select pk;
                query2 = from pk in query2
                         join sh in _shipmentRepository.Table on pk.ShipmentId equals sh.Id
                         join ord in _ordersRepository.Table on sh.OrderId equals ord.Id
                         where (ord.CustomerId == CustomerId) && ord.BoundOrderId == null
                         select pk;
            }
            if (startTime.HasValue)
            {
                query = from pk in query
                        where (pk.CreatedOnUtc >= startTime.Value)
                        select pk;
                query2 = from pk in query2
                         where (pk.CreatedOnUtc >= startTime.Value)
                         select pk;
            }
            if (endTime.HasValue)
            {
                query = from pk in query
                        where (pk.CreatedOnUtc <= endTime.Value)
                        select pk;
                query2 = from pk in query2
                         where (pk.CreatedOnUtc <= endTime.Value)
                         select pk;
            }

            if (shippingStatuses != null && shippingStatuses.Count() > 0)
            {
                query = from pk in query
                        where (shippingStatuses.Contains(pk.Status))
                        select pk;
                query2 = from pk in query2
                         where (shippingStatuses.Contains(pk.Status))
                         select pk;
            }

            if (!String.IsNullOrEmpty(postalCode))
            {
                query = from pk in query
                        where pk.PickUpLocation.Address.ZipPostalCode.Contains(postalCode) ||
                        pk.DeliveryLocation.Address.ZipPostalCode.Contains(postalCode)
                        select pk;
                query2 = from pk in query2
                         where pk.PickUpLocation.Address.ZipPostalCode.Contains(postalCode) ||
                         pk.DeliveryLocation.Address.ZipPostalCode.Contains(postalCode)
                         select pk;
            }

            if (districtId.HasValue && districtId != 0)
            {
                query = from pk in query
                        where (pk.PickUpLocation.DistrictId == districtId.Value || pk.DeliveryLocation.DistrictId == districtId.Value)
                        select pk;
                query2 = from pk in query2
                         where (pk.PickUpLocation.DistrictId == districtId.Value || pk.DeliveryLocation.DistrictId == districtId.Value)
                         select pk;
            }

            if (!String.IsNullOrEmpty(trackingNumber))
            {
                query = from pk in query
                        where pk.Shipment.TrackingNumber.Contains(trackingNumber)
                        select pk;
                query2 = from pk in query2
                         where pk.Shipment.TrackingNumber.Contains(trackingNumber)
                         select pk;

            }

            if (pickupDistrictId.HasValue)
            {
                query = from pk in query
                        where pk.PickUpLocation.DistrictId == pickupDistrictId.Value
                        select pk;
                query2 = from pk in query2
                         where pk.PickUpLocation.DistrictId == pickupDistrictId.Value
                         select pk;
            }

            if (deliveryDistrictId.HasValue)
            {
                query = from pk in query
                        where pk.DeliveryLocation.DistrictId == deliveryDistrictId.Value
                        select pk;
                query2 = from pk in query2
                         where pk.DeliveryLocation.DistrictId == deliveryDistrictId.Value
                         select pk;
            }

            if (pickupZone != null)
            {
                // TODO: Only support Canada code for now.
                string cc = pickupZone.District.CountryCode.ToUpperInvariant();
                if (cc != "CA")
                {
                    throw new NopException("The country code ({0}) is invalid.", cc);
                }

                var codeInZone = pickupZone.GetZipPostalCodeArray();

                query = from pk in query
                        where codeInZone.Contains(pk.PickUpLocation.Address.ZipPostalCode.Substring(0, 3))
                        select pk;
                query2 = from pk in query2
                         where codeInZone.Contains(pk.PickUpLocation.Address.ZipPostalCode.Substring(0, 3))
                         select pk;
            }

            if (deliveryZone != null)
            {
                // TODO: Only support Canada code for now.
                string cc = deliveryZone.District.CountryCode.ToUpperInvariant();
                if (cc != "CA")
                {
                    throw new NopException("The country code ({0}) is invalid.", cc);
                }

                var codeInZone = deliveryZone.GetZipPostalCodeArray();

                query = from pk in query
                        where codeInZone.Contains(pk.DeliveryLocation.Address.ZipPostalCode.Substring(0, 3))
                        select pk;
                query2 = from pk in query2
                         where codeInZone.Contains(pk.DeliveryLocation.Address.ZipPostalCode.Substring(0, 3))
                         select pk;
            }

            if (pickupAddressType.HasValue)
            {
                query = from pk in query
                        where pk.PickUpLocation.Address.Type == pickupAddressType
                        select pk;
                query2 = from pk in query2
                         where pk.PickUpLocation.Address.Type == pickupAddressType
                         select pk;
            }

            if (deliveryAddressType.HasValue)
            {
                query = from pk in query
                        where pk.DeliveryLocation.Address.Type == pickupAddressType
                        select pk;
                query2 = from pk in query2
                         where pk.DeliveryLocation.Address.Type == pickupAddressType
                         select pk;
            }
            if (OrderKindId != null && OrderKindId.Count() != 0)
            {
                query = from pk in query
                        where OrderKindId.Contains(pk.OrderKindId)
                        select pk;
                query2 = from pk in query2
                         where OrderKindId.Contains(pk.OrderKindId)
                         select pk;
            }
            query = query.Union(query2);
            query = query.OrderByDescending(pk => pk.CreatedOnUtc);
            return new PagedList<LogisticsPackage>(query, pageIndex, pageSize);
        }

        public virtual IPagedList<LogisticsPackage> SearchPackages(
            int applicatorId,
            DateTime? startTime,
            DateTime? endTime,
            ShippingStatus[] shippingStatuses,
            string postalCode,
            string trackingNumber,
            int? districtId,
            int? pickupDistrictId,
            int? deliveryDistrictId,
            Zone pickupZone,
            Zone deliveryZone,
            AddressType? pickupAddressType,
            AddressType? deliveryAddressType,
            int[] OrderKindId,
            int pageIndex, int pageSize)
        {
            var query = from pk in _packageRepository.Table
                        select pk;

            if (applicatorId != 0)
            {
                query = from pk in query
                        where (pk.ApplicatorId == applicatorId)
                        select pk;
            }

            if (startTime.HasValue)
            {
                query = from pk in query
                        where (pk.CreatedOnUtc >= startTime.Value)
                        select pk;
            }

            if (endTime.HasValue)
            {
                query = from pk in query
                        where (pk.CreatedOnUtc <= endTime.Value)
                        select pk;
            }

            if (shippingStatuses != null && shippingStatuses.Count() > 0)
            {
                query = from pk in query
                        where (shippingStatuses.Contains(pk.Status))
                        select pk;
            }

            if (!String.IsNullOrEmpty(postalCode))
            {
                query = from pk in query
                        where pk.PickUpLocation.Address.ZipPostalCode.Contains(postalCode) ||
                        pk.DeliveryLocation.Address.ZipPostalCode.Contains(postalCode)
                        select pk;
            }

            if (districtId.HasValue && districtId != 0)
            {
                query = from pk in query
                        where (pk.PickUpLocation.DistrictId == districtId.Value || pk.DeliveryLocation.DistrictId == districtId.Value)
                        select pk;
            }

            if (!String.IsNullOrEmpty(trackingNumber))
            {
                query = from pk in query
                        where pk.Shipment.TrackingNumber.Contains(trackingNumber)
                        select pk;
            }

            if (pickupDistrictId.HasValue)
            {
                query = from pk in query
                        where pk.PickUpLocation.DistrictId == pickupDistrictId.Value
                        select pk;
            }

            if (deliveryDistrictId.HasValue)
            {
                query = from pk in query
                        where pk.DeliveryLocation.DistrictId == deliveryDistrictId.Value
                        select pk;
            }

            if (pickupZone != null)
            {
                // TODO: Only support Canada code for now.
                string cc = pickupZone.District.CountryCode.ToUpperInvariant();
                if (cc != "CA")
                {
                    throw new NopException("The country code ({0}) is invalid.", cc);
                }

                var codeInZone = pickupZone.GetZipPostalCodeArray();

                query = from pk in query
                        where codeInZone.Contains(pk.PickUpLocation.Address.ZipPostalCode.Substring(0, 3))
                        select pk;
            }

            if (deliveryZone != null)
            {
                // TODO: Only support Canada code for now.
                string cc = deliveryZone.District.CountryCode.ToUpperInvariant();
                if (cc != "CA")
                {
                    throw new NopException("The country code ({0}) is invalid.", cc);
                }

                var codeInZone = deliveryZone.GetZipPostalCodeArray();

                query = from pk in query
                        where codeInZone.Contains(pk.DeliveryLocation.Address.ZipPostalCode.Substring(0, 3))
                        select pk;
            }

            if (pickupAddressType.HasValue)
            {
                query = from pk in query
                        where pk.PickUpLocation.Address.Type == pickupAddressType
                        select pk;
            }

            if (deliveryAddressType.HasValue)
            {
                query = from pk in query
                        where pk.DeliveryLocation.Address.Type == pickupAddressType
                        select pk;
            }
            if (OrderKindId != null && OrderKindId.Count() != 0)
            {
                query = from pk in query
                        where OrderKindId.Contains(pk.OrderKindId)
                        select pk;
            }

            query = query.OrderByDescending(pk => pk.CreatedOnUtc);
            return new PagedList<LogisticsPackage>(query, pageIndex, pageSize);
        }

        public virtual IPagedList<LogisticsPackage> SearchTrackingPackagesByCustomerId(
            int customerId,
            DateTime? startTime,
            DateTime? endTime,
            string trackingNumber,
            int pageIndex, int pageSize, bool includeDelivered = false)
        {
            var trackingStatuses = new List<ShippingStatus>() {
                ShippingStatus.NotYetReady,
                ShippingStatus.Delivered,
                ShippingStatus.ReadyForPickUp,
                ShippingStatus.Delivering,
                ShippingStatus.InWarehouse,
                ShippingStatus.InWarehouseFailedDelivery,
                ShippingStatus.ReturningWarehouse,
                ShippingStatus.ReturningWarehouseFailedDelivery,
                ShippingStatus.FailedPickUp,
                ShippingStatus.Cancelled
            };

            if (includeDelivered)
            {
                trackingStatuses.Add(ShippingStatus.Delivered);
            }

            return SearchPackagesByCustomerId(
                customerId,
                startTime, endTime,
                trackingStatuses.ToArray(), null, trackingNumber, null,
                null, null, null, null, null, null, null, pageIndex, pageSize);
        }

        public virtual IPagedList<LogisticsPackage> SearchTrackingPackages(
            int applicatorId,
            DateTime? startTime,
            DateTime? endTime,
            string trackingNumber,
            int pageIndex, int pageSize, bool includeDelivered = false)
        {
            var trackingStatuses = new List<ShippingStatus>() {
                ShippingStatus.NotYetReady,
                ShippingStatus.Delivered,
                ShippingStatus.ReadyForPickUp,
                ShippingStatus.Delivering,
                ShippingStatus.InWarehouse,
                ShippingStatus.InWarehouseFailedDelivery,
                ShippingStatus.ReturningWarehouse,
                ShippingStatus.ReturningWarehouseFailedDelivery,
                ShippingStatus.FailedPickUp,
                ShippingStatus.Cancelled
            };

            if (includeDelivered)
            {
                trackingStatuses.Add(ShippingStatus.Delivered);
            }

            return SearchPackages(
                applicatorId,
                startTime, endTime,
                trackingStatuses.ToArray(), null, trackingNumber, null,
                null, null, null, null, null, null, null, pageIndex, pageSize);
        }

        /// <summary>
        /// Gets the shipment by tracking number
        /// </summary>
        /// <param name="trackingNumber">Tracking number</param>
        /// <returns></returns>
        public virtual Shipment GetShipmentByTrackingNumber(string trackingNumber)
        {
            var shipments = _shipmentService.GetShipmentByTrackingNumber(trackingNumber).Where(s => s.Order.OrderKind == OrderKind.Express).ToList();
            if (shipments.Count < 1)
                return null;

            return shipments.First();
        }

        public void UpdatePackage(LogisticsPackage package, bool createPackageNote = true)
        {
            _packageRepository.Update(package);

            if (createPackageNote)
            {
                CreatePackageStatusChangeNote(package, false, null);
            }
        }

        public void UpdateLogisticsPackage(LogisticsPackage package, PlanNoteStatus noteStatus, int actorCustomerId, bool displayToCourier, bool validForCommission, bool createLog)
        {
            if (package == null)
                throw new ArgumentNullException("package");

            var plan = PackageLatestPlan(package);   // could be null!
            var locationId = PackageAssociatedLocation(package).Id;
            string note = "Package Updated"; // default, nothing to add.

            package.UpdatedOnUtc = DateTime.UtcNow;
            _packageRepository.Update(package);

            if (package.Status == ShippingStatus.Delivered &&
                        package.Shipment.Order.BoundOrderId.HasValue)
            {
                var commerceOrder = _orderService.GetOrderById(package.Shipment.Order.BoundOrderId.Value);
                if (commerceOrder != null)
                {
                    commerceOrder.ShippingStatus = ShippingStatus.Delivered;
                    if (commerceOrder.PaymentStatus == Core.Domain.Payments.PaymentStatus.Paid)
                    {
                        commerceOrder.OrderStatus = Core.Domain.Orders.OrderStatus.Complete;
                    }
                    _orderService.UpdateOrder(commerceOrder);
                }
            }

            var address = PackageAssociatedAddress(package);

            if ((createLog) && (plan != null))
            {
                CreatePlanNote(noteStatus, plan.Id, plan.Status, actorCustomerId, validForCommission, displayToCourier, package.Id, package.Status, locationId, address);
            }

            if (createLog)
            {

                // In such cases, we want to have a note for clarity.
                if ((noteStatus == PlanNoteStatus.UndoPackagePickup) || (noteStatus == PlanNoteStatus.UndoPackageDelivery))
                {
                    note = PlanNoteCommon.PlanNoteStatusString[(int)noteStatus];
                }
                else if (package.Status == ShippingStatus.Cancelled)
                {
                    note = GetPackageNoteContentByStatus(ShippingStatus.Cancelled, _workContext.WorkingLanguage.Id);
                }
                else if (package.Status == ShippingStatus.Cancelled)
                {
                    note = GetPackageNoteContentByStatus(package.Status, _workContext.WorkingLanguage.Id);
                }

                CreatePackageNote(package.Id, PackageNoteType.SystemEvent, note, actorCustomerId, true, package.Status, address);
            }
        }

        public void DeleteLogisticsPackage(int packageId)
        {
            if (packageId <= 0)
                throw new ArgumentException("package");

            var pkg = _packageRepository.GetById(packageId);
            if (pkg != null)
            {
                _packageRepository.Delete(pkg);
            }
        }

        public TimeSpan GetPackageElapsedTimeSinceOrdered(int id, DateTime? referenceTimeUtc)
        {
            var pkg = GetPackageById(id);
            if (pkg == null)
                throw new NopException(String.Format("Package not found for package Id: {0}", id));

            return GetPackageElapsedTimeSinceOrdered(pkg, referenceTimeUtc);
        }

        public TimeSpan GetPackageElapsedTimeSinceOrdered(LogisticsPackage pkg, DateTime? referenceTimeUtc)
        {
            if (pkg == null)
                throw new ArgumentNullException("pkg");

            var refTimeUtc = DateTime.UtcNow;
            if (referenceTimeUtc.HasValue)
                refTimeUtc = referenceTimeUtc.Value;

            var timeDiff = refTimeUtc - pkg.Shipment.Order.CreatedOnUtc;
            return timeDiff;
        }

        public bool PackageStatusUpdateable(LogisticsPackage pkg, Customer customer)
        {
            if (!_logisticsInformationSettings.BlockQuickStatusUpdate)
            {
                return true;
            }

            if (pkg == null)
                throw new ArgumentNullException("pkg");

            var plan = PackageLatestPlan(pkg);
            if (plan == null)
            {
                throw new NopException("Package ({0}) is not in a plan.", pkg.Id);
            }

            if (customer == null)
                throw new ArgumentNullException("customer");

            var coolDownExpired = true; // default: can update package status
            string customerIdStr = String.Format("|{0}|", customer.Id);
            var updateCoolDown = new TimeSpan(0, 2, 0);

            var query = from pn in _planNoteRepository.Table
                        where pn.LocationId.HasValue &&
                            (pn.LocationId.Value == pkg.PickUpLocation.Id || pn.LocationId.Value == pkg.DeliveryLocation.Id) &&
                            pn.Note.Contains(customerIdStr) &&
                            pn.PlanId == plan.Id
                        orderby pn.CreatedOnUtc descending
                        select pn;

            var notes = query.ToList();

            foreach (var pn in notes)
            {
                PlanNoteStatus action;
                PlanStatus planStatus;
                int customerId = -1;
                int? packageId = -1;
                ShippingStatus? packageStatus;
                int? addressId = null;

                pn.ExtractPlanNoteStringContent(out action, out planStatus, out customerId,
                                                out packageId, out packageStatus, out addressId);
                if (customerId == customer.Id)
                {
                    if (DateTime.UtcNow - pn.CreatedOnUtc < updateCoolDown)
                    {
                        coolDownExpired = false;
                    }
                    break;
                }
            }

            return coolDownExpired;
        }

        public int CreateShipment(int applicatorId, Address fromAddr, Address toAddr, int[] timeWindowIds, Shipment shipment, int orderKindId, DateTime? readyTime = null, DateTime? pickupEndTime = null)
        {
            var profiler = MiniProfiler.Current;

            using (profiler.Step("CreateShipment"))
            {
                if (fromAddr.Id == 0)
                {
                    _addressSerivce.InsertAddress(fromAddr);
                }
                var fromZone = GetZoneByZipPostalCode(fromAddr.ZipPostalCode, fromAddr.Country.TwoLetterIsoCode);
                var pickupLocation = new LogisticsLocation()
                {
                    Address = fromAddr,
                    CreatedOnUtc = DateTime.UtcNow,
                    Status = LocationStatus.ReadyForPickUp,
                    DistrictId = fromZone == null ? null : (int?)fromZone.DistrictId
                };
                _locationRepository.Insert(pickupLocation);

                if (toAddr.Id == 0)
                {
                    _addressSerivce.InsertAddress(toAddr);
                }
                var toZone = GetZoneByZipPostalCode(toAddr.ZipPostalCode, toAddr.Country.TwoLetterIsoCode);
                var deliveryLocation = new LogisticsLocation()
                {
                    Address = toAddr,
                    CreatedOnUtc = DateTime.UtcNow,
                    Status = LocationStatus.PendingDropOff,
                    DistrictId = toZone == null ? null : (int?)toZone.DistrictId
                };
                _locationRepository.Insert(deliveryLocation);

                var package = new LogisticsPackage()
                {
                    Status = ShippingStatus.ReadyForPickUp,
                    CreatedOnUtc = DateTime.UtcNow,
                    PickUpLocation = pickupLocation,
                    DeliveryLocation = deliveryLocation,
                    Shipment = shipment,
                    ApplicatorId = applicatorId,
                    ReadyOnUtc = readyTime,
                    PickupEndOnUtc = pickupEndTime,
                    DeliveryAttemptCount = 0,
                    OrderKindId = orderKindId
                };
                if (package.ReadyOnUtc.HasValue)
                {
                    package.Status = ShippingStatus.NotYetReady;
                }

                var order = shipment.Order;
                if (order != null && order.BoundOrderId == null)
                {
                    package.Status = (ShippingStatus)order.ShippingStatusId;
                }

                _packageRepository.Insert(package);

                pickupLocation.Package = package;
                _locationRepository.Update(pickupLocation);

                deliveryLocation.Package = package;
                _locationRepository.Update(deliveryLocation);

                var zoneStr = toZone == null ? "00000" : toZone.Name;

                // TODO: Zone name should be generated according to the postal code?
                shipment.TrackingNumber = GenerateTrackingNumber(
                    zoneStr,
                    toAddr.ZipPostalCode,
                    applicatorId,
                    shipment.OrderId,
                    package.Id);
                _shipmentService.UpdateShipment(shipment);

                return package.Id;
            }
        }

        public bool PackageAtPickupLocation(LogisticsPackage package)
        {
            if (package == null)
                throw new ArgumentNullException("package");

            var retValue = package.Status == ShippingStatus.ReadyForPickUp ||
                           package.Status == ShippingStatus.FailedPickUp;

            return retValue;
        }

        public bool PackageInWarehouse(LogisticsPackage package, bool checkStatusOnly = false)
        {
            if (package == null)
                throw new ArgumentNullException("package");

            // technically, just having the WarehouseId.HasValue should be good enough.
            // When warehouseId.HasValue, the status has to be one of these too. So, might as well.
            var retValue = (package.Status == ShippingStatus.InWarehouse ||
                           package.Status == ShippingStatus.InWarehouseFailedDelivery) &&
                           (checkStatusOnly || package.WarehouseId.HasValue);

            return retValue;
        }

        public bool PackagePickedUp(LogisticsPackage package)
        {
            if (package == null)
                throw new ArgumentNullException("package");

            if (!PackageAtPickupLocation(package))
            {
                return true;
            }

            return false;
        }

        public bool PackageInTransientState(LogisticsPackage package)
        {
            if (package == null)
                throw new ArgumentNullException("package");

            // NOTE: Currently, Cancelled is supposed to be used only when package has not been
            //       picked up!
            if (package.Status != ShippingStatus.ReadyForPickUp &&
                package.Status != ShippingStatus.FailedPickUp &&
                package.Status != ShippingStatus.InWarehouse &&
                package.Status != ShippingStatus.InWarehouseFailedDelivery &&
                package.Status != ShippingStatus.Delivered &&
                package.Status != ShippingStatus.Cancelled)
            {
                return true;
            }

            return false;
        }

        public int PackagesInTransientState(List<LogisticsPackage> pkgList)
        {
            var packagesInTransientState = pkgList.Where(pkg => PackageInTransientState(pkg))
                                                  .Count();
            return packagesInTransientState;
        }

        public bool PackageInStaticStateButStillInProcess(LogisticsPackage package)
        {
            if (package == null)
                throw new ArgumentNullException("package");

            if (!(package.Status == ShippingStatus.ReturningWarehouse
                || package.Status == ShippingStatus.Delivering
                || package.Status == ShippingStatus.ReturningWarehouseFailedDelivery
                || package.Status == ShippingStatus.Delivered))
            {
                return true;
            }

            return false;
        }

        public bool PackageOnWayToWarehouse(LogisticsPackage package)
        {
            if (package == null)
                throw new ArgumentNullException("package");

            if (package.Status == ShippingStatus.ReturningWarehouse ||
                package.Status == ShippingStatus.ReturningWarehouseFailedDelivery)
            {
                return true;
            }

            return false;
        }

        public bool PackagePickupAttempted(LogisticsPackage package)
        {
            if (package == null)
                throw new ArgumentNullException("package");

            if (package.Status == ShippingStatus.FailedPickUp ||
                PackagePickedUp(package))
            {
                return true;
            }

            return false;
        }

        public bool PackageDeliveryAttempted(LogisticsPackage package)
        {
            if (package == null)
                throw new ArgumentNullException("package");

            if (package.Status == ShippingStatus.ReturningWarehouseFailedDelivery ||
                package.Status == ShippingStatus.Delivered)
            {
                return true;
            }

            return false;
        }

        public LogisticsPlan PackageLatestPlan(LogisticsPackage package)
        {
            if (package == null)
                throw new ArgumentNullException("package");

            LogisticsPlan plan = null;

            if (package.DeliveryLocation.PlanId.HasValue)
            {
                plan = package.DeliveryLocation.Plan;
            }
            else if (package.PickUpLocation.PlanId.HasValue)
            {
                plan = package.PickUpLocation.Plan;
            }

            return plan;
        }

        // TODO: Do we need some more thought as to the case where package starts from warehouse and going to another warehouse?
        // NOTE: This method is not really that good anymore with the newer requirements. So, use with caution.
        //       Where applicable, should use PackageAssociatedAddress() instead.
        public LogisticsLocation PackageAssociatedLocation(LogisticsPackage package)
        {
            if (package == null)
                throw new ArgumentNullException("package");

            LogisticsLocation loc = null;

            if (package.Status == ShippingStatus.ReadyForPickUp ||
                package.Status == ShippingStatus.InWarehouse ||
                package.Status == ShippingStatus.FailedPickUp ||
                package.Status == ShippingStatus.ReturningWarehouse)
            {
                loc = package.PickUpLocation;
            }
            else
            {
                loc = package.DeliveryLocation;
            }
            return loc;
        }

        public Address PackageAssociatedAddress(LogisticsPackage package)
        {
            if (package == null)
                throw new ArgumentNullException("package");

            Address addr = null;

            if (package.Status == ShippingStatus.ReadyForPickUp ||
                package.Status == ShippingStatus.FailedPickUp ||
                package.Status == ShippingStatus.NotYetReady)
            {
                addr = package.PickUpLocation.Address;
            }
            else if (package.Status == ShippingStatus.ReturningWarehouse ||
                     package.Status == ShippingStatus.ReturningWarehouseFailedDelivery)
            {
                if (package.ToWarehouseId.HasValue)
                {
                    addr = package.ToWarehouse.Address;
                }
                else
                {
                    if (package.DeliveryLocation.PlanId.HasValue)
                    {
                        addr = package.DeliveryLocation.Plan.Warehouse.Address;
                    }
                    else if (package.PickUpLocation.PlanId.HasValue)
                    {
                        addr = package.PickUpLocation.Plan.Warehouse.Address;
                    }
                    else // should really not get here!!
                    {
                        // but if it does, then remains null.
                    }
                }
            }
            else if (package.Status == ShippingStatus.InWarehouse ||
                     package.Status == ShippingStatus.InWarehouseFailedDelivery)
            {
                if (package.WarehouseId.HasValue)
                {
                    addr = package.Warehouse.Address;
                }
                else // should really not get here!
                {
                    if (package.DeliveryLocation.PlanId.HasValue)
                    {
                        addr = package.DeliveryLocation.Plan.Warehouse.Address;
                    }
                    else if (package.PickUpLocation.PlanId.HasValue)
                    {
                        addr = package.PickUpLocation.Plan.Warehouse.Address;
                    }
                    else
                    {
                        // remains null then.
                    }
                }
            }
            else if (package.Status == ShippingStatus.Delivering ||
                     package.Status == ShippingStatus.Delivered)
            {
                addr = package.DeliveryLocation.Address;
            }

            return addr;
        }

        public bool ValidForCommission(LogisticsPackage package, ShippingStatus pkgPreviousStatus)
        {
            if (package == null)
                throw new ArgumentNullException("package");

            var validForCommission = false; // default to false, update below as appropriate

            var pkgStatus = package.Status;
            var plan = PackageLatestPlan(package); // could be null.

            if (pkgStatus == ShippingStatus.FailedPickUp ||
                pkgStatus == ShippingStatus.ReturningWarehouse)
            {
                if (pkgPreviousStatus == ShippingStatus.ReadyForPickUp)
                {
                    validForCommission = true;
                }
            }
            else if (pkgStatus == ShippingStatus.Delivering)
            {
                // case when going straight to delivering mode after pick up 
                // (pickup and deliver under same plan).
                // In this case, Delivering status also denotes successful pickup and count
                // for commission!
                if (pkgPreviousStatus == ShippingStatus.ReadyForPickUp)
                {
                    validForCommission = true;
                }
            }
            else if (pkgStatus == ShippingStatus.Delivered ||
                     pkgStatus == ShippingStatus.ReturningWarehouseFailedDelivery)
            {
                validForCommission = true;
            }
            else if (pkgStatus == ShippingStatus.InWarehouse)
            {
                if (package.WarehouseId.HasValue && plan != null && plan.WarehouseId.HasValue && package.WarehouseId.Value != plan.WarehouseId.Value)
                {
                    // In this case, package has moved from one warehouse to another warehouse.
                    // The destination warehouseID must be different from the source warehouseID (which is plan's warehouse).
                    validForCommission = true;
                }
            }

            return validForCommission;
        }

        public bool PackageRanked(LogisticsPackage package, int planId)
        {
            if (package == null)
                throw new ArgumentNullException("package");

            var retValue = true;

            if ((package.DeliveryLocation.PlanId.HasValue && package.DeliveryLocation.PlanId.Value == planId &&
                 !package.DeliveryLocation.Rank.HasValue) ||
                (package.PickUpLocation.PlanId.HasValue && package.PickUpLocation.PlanId.Value == planId &&
                 !package.PickUpLocation.Rank.HasValue))
            {
                retValue = false;
            }

            return retValue;
        }

        public bool PackagesSameDistrict(IList<LogisticsPackage> packages)
        {
            if (packages == null)
                throw new ArgumentNullException("packages");

            var combinedIds = new List<int>(4);

            // check packages waiting for pickup to see if they are from more than one district.
            var packagesAtPickup = packages.Where(p => PackageAtPickupLocation(p)).Select(p => p);
            var pickupDistrictIds = packagesAtPickup.Select(p => GetZoneByZipPostalCode(p.PickUpLocation.Address.ZipPostalCode,
                                                                                        p.PickUpLocation.Address.Country.TwoLetterIsoCode).DistrictId).Distinct();

            if (pickupDistrictIds.Count() > 1)
                return false;

            if (pickupDistrictIds.Count() > 0)
                combinedIds.Add(pickupDistrictIds.First());

            // for delivery means that package not planned to be going to another warehouse (don't care where it is coming from).
            var packagesForDelivery = packages.Where(p => !p.ToWarehouseId.HasValue).Select(p => p);
            var deliveryDistrictIds = packagesForDelivery.Select(p => GetZoneByZipPostalCode(p.DeliveryLocation.Address.ZipPostalCode,
                                                                                             p.DeliveryLocation.Address.Country.TwoLetterIsoCode).DistrictId).Distinct();

            if (deliveryDistrictIds.Count() > 1)
                return false;

            if (deliveryDistrictIds.Count() > 0)
                combinedIds.Add(deliveryDistrictIds.First());

            // check packages that are planned to head to (another) warehouse
            var packagesToPlannedWarehouses = packages.Where(p => p.ToWarehouseId.HasValue).Select(p => p);
            var plannedWarehouseDistrictIds = packagesToPlannedWarehouses.Select(p => GetZoneByZipPostalCode(p.ToWarehouse.Address.ZipPostalCode,
                                                                                                             p.ToWarehouse.Address.Country.TwoLetterIsoCode).DistrictId).Distinct();

            if (plannedWarehouseDistrictIds.Count() > 1)
                return false;

            if (plannedWarehouseDistrictIds.Count() > 0)
                combinedIds.Add(plannedWarehouseDistrictIds.First());

            // check packages that are actually still sitting in warehouses
            var packagesInWarehouses = packages.Where(p => PackageInWarehouse(p)).Select(p => p);
            var inWarehouseDistrictIds = packagesInWarehouses.Select(p => GetZoneByZipPostalCode(p.Warehouse.Address.ZipPostalCode,
                                                                                                 p.Warehouse.Address.Country.TwoLetterIsoCode).DistrictId).Distinct();

            if (inWarehouseDistrictIds.Count() > 1)
                return false;

            if (inWarehouseDistrictIds.Count() > 0)
                combinedIds.Add(inWarehouseDistrictIds.First());

            // Now that each of the cases above may return up to one districID, it is time to check
            // if they are all one and the same districID.
            var distinctId = combinedIds.Distinct();
            if (distinctId.Count() > 1)
                return false;

            return true;
        }

        public bool PackagesInSameWarehouse(IList<LogisticsPackage> packages, out int theWarehouseId)
        {
            if (packages == null)
                throw new ArgumentNullException("packages");

            theWarehouseId = -1;  // invalid value
            var retValue = true;

            // Isolate those packages that are actually in warehouses.
            var packagesInWarehouse = packages.Where(p => PackageInWarehouse(p)).Select(p => p);

            if (packagesInWarehouse.Count() == 0)
            {
                // no packages in warehouse, so just return true. But, theWarehouseId remains invalid.
                return retValue;
            }

            var warehouseIds = packagesInWarehouse.Select(p => p.WarehouseId.Value).Distinct();

            // if more than one warehouse IDs, then In-Warehouse packages are from multiple warehouses.
            // in this case, returns false with theWarehouseId remains -1 (invalid)
            if (warehouseIds.Count() > 1)
            {
                retValue = false;
            }
            else
            {
                // only one warehouse, thus return the Id.
                theWarehouseId = warehouseIds.ElementAt(0);
            }

            return retValue;
        }

        public const int MaxDeliveryAttemptCount = 2;

        /// <summary>
        /// Whether the package reaches the max delivery attempt
        /// </summary>
        /// <param name="package"></param>
        /// <returns></returns>
        public virtual bool IsMaxDeliveryAttempt(LogisticsPackage package)
        {
            // TODO: We should NOT hard code here.
            int failedTimes = GetFailedDeliveryEventCount(package);
            if (failedTimes >= MaxDeliveryAttemptCount)
            {
                return true;
            }
            else if (failedTimes == 1
                && package.DeliveryLocation.Address.Type == AddressType.Residential
                && (package.Shipment.Attributes.HasValue && ((AddtionalAttributes)package.Shipment.Attributes & AddtionalAttributes.NoSignature) == AddtionalAttributes.NoSignature))
            {
                return true;
            }

            return false;
        }

        /// <summary>
        /// get shipmentdate and timerange
        /// </summary>
        /// <returns></returns>
        public DatePickerModel GetPickerModel(int orderKind, AddressType addressType, string zipPostalCode, DateTime? inputDate = null)
        {
            var days = GetValidateDays(orderKind, addressType, zipPostalCode);

            if (days == null || days.Count <= 0) return new DatePickerModel();

            var model = new DatePickerModel()
            {
                MinTime = days.FirstOrDefault().Time.ToString("yyyy/MM/dd"),
                MaxTime = days.LastOrDefault().Time.ToString("yyyy/MM/dd"),
                DayNum = days.Count,
                DaysList = days.ToList(),
                Days = JsonConvert.SerializeObject(days),
                ShipmentValueId = days.FirstOrDefault().ShipmentTimeNum.ToString(),
                DisabledDates = string.Empty,
                IsInputDateValid = false
            };
            model.FirstAvailableTime = model.MinTime + " " + days.FirstOrDefault().ShipmentTimeSpans[0].Description;
            model.FirstAvailableDate = days.FirstOrDefault().Time;
            model.FirstAvailableTimeWindow = days.FirstOrDefault().ShipmentTimeSpans.FirstOrDefault().Id;
            if (inputDate.HasValue)
            {
                model.InputDate = inputDate.Value;
                model.IsInputDateValid = days.FirstOrDefault(d => d.Time == inputDate.Value) != null;
            }

            return model;
        }

        public IList<Day> GetValidateDays(int orderKind, AddressType addressType, string zipPostalCode)
        {
            return ReturnValidateDays(orderKind, addressType, zipPostalCode, DateTime.UtcNow);
        }

        private IList<Day> ReturnValidateDays(int orderKind, AddressType addressType, string zipPostalCode, DateTime utcTime)
        {
            var timeWindows = this.GetAllShippingTimeWindows();

            var timeValidatesTimeWindows = _shipingDayService.GetValidateShipmentTimeSpan(orderKind, (int)addressType, zipPostalCode, timeWindows);

            return _shipingDayService.GetValidateDays(timeValidatesTimeWindows, false, _dateTimeHelper.ConvertToUserTime(utcTime, DateTimeKind.Utc), 90);
        }

        public string GetPickUpTimeFromGenericAttribute(OrderKind orderKind)
        {
            var shipmentTime = _genericAttributeService.GetAttribute(_workContext.CurrentCustomer.Id, "Customer", SystemCustomerAttributeNames.ShipmentTime);

            if (shipmentTime != null) return shipmentTime.Value;

            return "";
            //var address = _workContext.CurrentCustomer.ShippingAddress;

            //address.Type = address.Type == AddressType.Unknown ? AddressType.Business : address.Type;

            //var day = GetValidateDays((int)orderKind, address.Type.Value, address.ZipPostalCode).FirstOrDefault();

            //return day == null ? string.Empty : string.Format("{0};{1}", day.Time, day.ShipmentTimeSpans.FirstOrDefault().Id);
        }

        /// <summary>
        /// show pick up time
        /// </summary>
        /// <param name="date">PickUp Date</param>
        /// <param name="timespanId">time area</param>
        /// <returns>pick up time</returns>
        private string GetShowPickUpTime(string date, int timespanId)
        {
            if (timespanId <= 0 || string.IsNullOrEmpty(date)) return string.Empty;

            var shippingTimeWindow = GetShippingTimeWindowById(timespanId);

            return shippingTimeWindow == null ? string.Empty : string.Format("{0} {1}", date, shippingTimeWindow.Description);
        }

        public string GetShowPickUpTime(DateTime? date, int? timespanId, Order order)
        {
            if (!date.HasValue || !timespanId.HasValue)
                return "";
            if (order == null)
                return GetShowPickUpTime(date.Value.Date.ToShortDateString(), timespanId.Value);

            if (order.BoundOrderId == null && order.OrderKindId == (int)OrderKind.Express)
            {
                if (!order.ShippingAddressId.HasValue) return string.Empty;

                var shipAddress = _addressSerivce.GetAddressById((int)order.ShippingAddressId);
                if (shipAddress == null) return string.Empty;

                var days = ReturnValidateDays((int)OrderKind.Express, shipAddress.Type.HasValue ? shipAddress.Type.Value : AddressType.Business, shipAddress.ZipPostalCode, _dateTimeHelper.ConvertToUtcTime(date.Value));

                if (days == null || days.Count < 2) return string.Empty;

                var shippingTimeWindow = GetShippingTimeWindowById(timespanId.Value);

                return shippingTimeWindow == null ? string.Empty : string.Format("{0} {1}", days[1].Time.Date.ToShortDateString(), shippingTimeWindow.Description);
            }
            else
            {
                return GetShowPickUpTime(date.Value.Date.ToShortDateString(), timespanId.Value);
            }
        }

        public virtual List<ShippingTimeWindow> GetAllShippingTimeWindows()
        {
            return _shippingTimeWindowRepository.Table.OrderBy(tw => tw.DisplayOrder).ToList();
        }

        public virtual ShippingTimeWindow GetShippingTimeWindowById(int id)
        {
            return _shippingTimeWindowRepository.GetById(id) ?? new ShippingTimeWindow();
        }

        public virtual bool IsEarliestShippingDate(Order order)
        {
            try
            {
                if (order == null || !order.ShippingTimeWindowsId.HasValue || order.ShippingTimeWindowsId <= 0 || !order.OrderKindId.HasValue || order.ShippingAddress == null) return false;

                var address = order.ShippingAddress;
                if (address == null || !address.Type.HasValue) return false;

                var days = GetValidateDays(order.OrderKindId.Value, address.Type.Value, address.ZipPostalCode);

                if (days == null || days.Count <= 0) return false;

                var earliestTimeSpan = days[0].ShipmentTimeSpans.FirstOrDefault();
                var earliestDate = days[0].Time.Date;
                if (earliestTimeSpan == null) return false;

                return order.ShipmentDate.HasValue && order.ShipmentDate.Value.Date == earliestDate && order.ShippingTimeWindowsId == earliestTimeSpan.Id;
            }
            catch (Exception exception)
            {
                _logger.InsertLog(Nop.Core.Domain.Logging.LogLevel.Error, exception.Message);
                return false;
            }
        }

        #endregion

        #region Logistics Plan

        public List<LogisticsPlan> GetPlans(PlanStatus[] planStatusArray,
                                            DateTime? startDate,
                                            DateTime? endDate,
                                            int? driverId)
        {
            var query = from p in _planRepository.Table
                        select p;

            if (planStatusArray != null && planStatusArray.Length > 0)
                query = query.Where(p => planStatusArray.Contains(p.Status));

            if (driverId.HasValue && driverId.Value > 0) // 0 is invalid DB table's key value
                query = query.Where(p => p.CourierId == driverId);

            if (startDate.HasValue)
                query = (from p in query
                         join pn in _planNoteRepository.Table on p.Id equals pn.PlanId
                         where pn.CreatedOnUtc >= startDate.Value
                         select p).Distinct();

            if (endDate.HasValue)
                query = from p in query
                        join pn in _planNoteRepository.Table on p.Id equals pn.PlanId
                        where pn.CreatedOnUtc <= endDate.Value
                        select p;



            //works fine in local, use this to make it work in dev
            return query.Distinct().ToList();
        }

        public LogisticsPlan GetPlanByDriverId(int driverId)
        {
            var query = from p in _planRepository.Table
                        where p.CourierId == driverId && p.Status != PlanStatus.Closed
                        select p;
            return query.FirstOrDefault();
        }

        public LogisticsPlan GetPlanByPlanId(int planId)
        {
            return _planRepository.GetById(planId);
        }

        public LogisticsPlan CreateLogisticsPlan(LogisticsCourier courier, LogisticsWarehouse warehouse, int actorCustomerId)
        {
            int planId = -1; // signifies invalid plan.

            var plan = new LogisticsPlan()
            {
                CourierId = courier.Id,
                WarehouseId = warehouse.Id,
                Status = PlanStatus.Locked,
            };

            _planRepository.Insert(plan);
            planId = plan.Id;

            CreatePlanNote(PlanNoteStatus.CreatePlan, plan.Id, plan.Status, actorCustomerId, false, true, null, null, null, null);

            return plan;
        }

        public void AssignLogisticsPlanToPackages(int[] packageIds, int planId, int customerId)
        {
            if (packageIds == null)
                throw new ArgumentNullException("packageIds");

            if (packageIds.Count() == 0)
                throw new ArgumentException("Empty list of pending packages provided");

            var plan = GetPlanByPlanId(planId);

            plan.Status = PlanStatus.Locked;
            _planRepository.Update(plan);

            CreatePlanNote(PlanNoteStatus.UpdatePlan, plan.Id, plan.Status, customerId, false, true, null, null, null, null);

            foreach (var pkgId in packageIds)
            {
                var pkg = GetPackageById(pkgId);

                var locationId = pkg.DeliveryLocationId;
                if (pkg.Status == ShippingStatus.ReadyForPickUp ||
                    (pkg.Status == ShippingStatus.NotYetReady && pkg.ReadyOnUtc.HasValue) ||
                    pkg.Status == ShippingStatus.FailedPickUp)
                {
                    // in this case, adding this package to a plan means the pickup
                    // still need to be planned (or re-planned). So rank must start from
                    // nothing in the (new) plan.
                    pkg.PickUpLocation.PlanId = planId;
                    pkg.PickUpLocation.CourierId = plan.CourierId;
                    MarkPackageAsReady(pkg, customerId); // once entered into plan, must be ready
                    pkg.PickUpLocation.Rank = null;
                    pkg.PickUpLocation.UpdatedOnUtc = DateTime.UtcNow;
                    locationId = pkg.PickUpLocationId;
                }

                /* Leave packages as InWarehouseFailedDelivery when adding to a new plan.
                
                else if (pkg.Status == ShippingStatus.InWarehouseFailedDelivery)
                {
                    // we must tranform status this way, otherwise, within a plan, we have no idea
                    // whether a InWarehouseFailedDelivery package started that way or ended that way.
                    // It will confuse the check for completed plan.
                    pkg.Status = ShippingStatus.InWarehouse;
                }
                */

                pkg.DeliveryLocation.PlanId = planId;
                pkg.DeliveryLocation.CourierId = plan.CourierId;
                pkg.DeliveryLocation.Rank = null;
                pkg.DeliveryLocation.UpdatedOnUtc = DateTime.UtcNow;
                _packageRepository.Update(pkg);

                var addr = PackageAssociatedAddress(pkg);

                CreatePlanNote(PlanNoteStatus.AddPackage, plan.Id, plan.Status, customerId, false, true, pkgId, pkg.Status, locationId, addr);
            }
        }

        public void RemovePackagesFromLogisticsPlan(int[] packageIds, int planId, int actorCustomerId)
        {
            if (packageIds == null)
                throw new ArgumentNullException("packageIds");

            if (packageIds.Count() == 0)
                throw new ArgumentException("Empty list of pending packages provided");

            LogisticsPlan plan = GetPlanByPlanId(planId);

            foreach (var pkgId in packageIds)
            {
                var pkg = GetPackageById(pkgId);

                if (pkg.PickUpLocation.PlanId.HasValue &&
                    pkg.PickUpLocation.PlanId.Value == planId)
                {
                    pkg.PickUpLocation.PlanId = null;
                    pkg.PickUpLocation.CourierId = null;
                    pkg.PickUpLocation.Rank = null;
                    pkg.PickUpLocation.UpdatedOnUtc = DateTime.UtcNow;
                }
                if (pkg.DeliveryLocation.PlanId.HasValue &&
                    pkg.DeliveryLocation.PlanId.Value == planId)
                {
                    pkg.DeliveryLocation.PlanId = null;
                    pkg.DeliveryLocation.CourierId = null;
                    pkg.DeliveryLocation.Rank = null;
                    pkg.DeliveryLocation.UpdatedOnUtc = DateTime.UtcNow;
                }

                /* should not need this. The Warehouse should be assigned when package is checked in to warehouse
                if (PackageInWarehouse(pkg) && pkg.Warehouse == null && !pkg.WarehouseId.HasValue)
                {
                    pkg.WarehouseId = plan.Warehouse.Id;
                    pkg.Warehouse = plan.Warehouse;
                }
                */
                _packageRepository.Update(pkg);

                var locationId = PackageAssociatedLocation(pkg).Id;
                var addr = PackageAssociatedAddress(pkg);

                CreatePlanNote(PlanNoteStatus.RemovePackage, planId, plan.Status, actorCustomerId, false, true, pkg.Id, pkg.Status, locationId, addr);
            }
        }

        public bool LogisticsPlanCompleted(int planId)
        {
            bool planCompleted = true;

            var pkgs = GetPackagesByPlanId(planId);
            if (pkgs == null)
                throw new Exception(String.Format("No Logistics Packages can be found for Logistics PlanID: {0}", planId));

            var packagesBeingServicedStatus = new ShippingStatus[] { ShippingStatus.ReadyForPickUp, ShippingStatus.ReturningWarehouse,
                                                                     ShippingStatus.Delivering, ShippingStatus.ReturningWarehouseFailedDelivery };

            // Either a package is "obviously" being serviced, OR,
            // It is "starting out" from InWarehouse state!
            // In these cases, plan is not completed.
            planCompleted = !pkgs.Any(p => packagesBeingServicedStatus.Contains(p.Status) ||
                                      (p.Status == ShippingStatus.InWarehouse && (!p.PickUpLocation.PlanId.HasValue || p.PickUpLocation.PlanId.Value != planId) && p.DeliveryLocation.Rank.HasValue));

            return planCompleted;
        }

        public void MarkLogisticsPlanAsCompleted(int planId, int customerId)
        {
            var plan = GetPlanByPlanId(planId);
            if (plan == null)
                throw new Exception(String.Format("No Logistics Plan can be found for planID: {0}", planId));

            plan.Status = PlanStatus.Closed;
            UpdateLogisticsPlan(plan, customerId);
        }

        public void UpdateLogisticsPlan(LogisticsPlan plan, int actorCustomerId)
        {
            if (plan == null)
                throw new ArgumentNullException("plan");

            _planRepository.Update(plan);

            CreatePlanNote(PlanNoteStatus.UpdatePlan, plan.Id, plan.Status, actorCustomerId, false, true, null, null, null, null);
        }

        public IList<string> ValidateLogisticsPlan(LogisticsPlan plan)
        {
            if (plan == null)
                throw new ArgumentNullException("plan");

            IList<string> errorList = new List<string>(0);

            // logistic locations marked as part of this plan, if any, must have been ranked.
            var locations = GetLogisticsLocationsByPlanId(plan.Id);
            foreach (var loc in locations)
            {
                if (!loc.Rank.HasValue)
                {
                    errorList.Add(String.Format("Location Id {0} has no Rank.", loc.Id));
                }
            }

            return errorList;
        }

        public bool PlanAccessibleToDistrict(LogisticsPlan plan, District district)
        {
            if (plan == null)
                throw new ArgumentNullException("plan");

            if (district == null)
                throw new ArgumentNullException("district");

            // a plan is tied to "a" warehouse, and its the warehouse that is associated
            // with a district. This is the obvious case of a plan accessible to a given district.
            if (plan.Warehouse.DistrictId == district.Id)
                return true;

            // the rest is to check the case when packages are coming in to a district
            // (or warehouse in the district) from outside of the district.
            var locs = GetLogisticsLocationsByPlanId(plan.Id);

            if (locs.Count == 0)
                return false;

            foreach (var loc in locs)
            {
                if ((loc.Package.ToWarehouse != null && loc.Package.ToWarehouse.District.Id == district.Id) ||
                    (loc.Package.Warehouse != null && loc.Package.Warehouse.District.Id == district.Id))
                {
                    return true;
                }
            }

            /*
            // to support multi-district delivery WITH intermediate district stop-over,
            // then make the plan accessible to any district within the same region.
            if (plan.Warehouse.District.RegionId != district.RegionId)
                return false;
            */

            return false;
        }

        #endregion

        #region Logistics Location & Warehouse

        public LogisticsLocation GetLogisticsLocationById(int locationId)
        {
            var query = from loc in _locationRepository.Table
                        where loc.Id == locationId
                        select loc;
            return query.SingleOrDefault();
        }

        /// <summary>
        /// Get a list of Logistics Locations associated with a Plan ID.
        /// Assumes that the locations have all been properly tagged with Plan ID. Otherwise,
        /// those locations will not be part of the returned list!.
        /// The logistics locations will be ordered in ascending order of Rank value.
        /// </summary>
        /// <param name="planId">Plan Id</param>
        /// <returns>List of logistics locations</returns>
        public List<LogisticsLocation> GetLogisticsLocationsByPlanId(int planId)
        {
            if (planId <= 0)
                throw new Exception("Invalid planId = " + planId.ToString());

            // order the list such that null Rank is last. Where rank is not null,
            // ascending ordering is applied.
            var query = from loc in _locationRepository.Table
                        where loc.PlanId.HasValue && loc.PlanId.Value == planId
                        orderby loc.Rank != null ? 1 : 0 descending,
                        loc.Rank
                        select loc;

            var locations = query.ToList();
            return locations;
        }

        public void UpdateLogisticsLocation(LogisticsLocation location)
        {
            if (location == null)
                throw new ArgumentNullException("location");

            _locationRepository.Update(location);
        }

        public LogisticsWarehouse GetWarehouseById(int id)
        {
            var query = from w in _warehouseRepository.Table
                        where w.Id == id
                        select w;
            return query.SingleOrDefault();
        }

        public virtual void AddWarehouse(LogisticsWarehouse warehouse)
        {
            _warehouseRepository.Insert(warehouse);
        }

        /// <summary>
        /// Gets all warehouses within a district
        /// </summary>
        /// <param name="district">null indicates to get all warehouses</param>
        /// <returns></returns>
        public virtual List<LogisticsWarehouse> GetAllWarehouses(District district)
        {
            var query = from w in _warehouseRepository.Table
                        select w;

            if (district != null)
            {
                query = from w in query
                        where w.DistrictId == district.Id
                        select w;
            }

            return query.ToList();
        }

        /// <summary>
        /// Gets all warehouses within a region
        /// </summary>
        /// <param name="region">The region</param>
        /// <returns></returns>
        public virtual List<LogisticsWarehouse> GetAllWarehousesInRegion(Region region)
        {
            if (region == null)
                throw new ArgumentNullException("region");

            string key = string.Format(LOGISTICSWAREHOUSES_BY_REGIONID_KEY, region.Id);
            return _cacheManager.Get(key, () =>
            {
                var query = from w in _warehouseRepository.Table
                            where w.District.RegionId == region.Id
                            select w;

                return query.ToList();
            });
        }

        protected virtual LogisticsWarehouse GetWarehouse()
        {
            return _warehouseRepository.Table.First();
        }

        public LogisticsWarehouse PackageInThisWarehouse(LogisticsPackage package)
        {
            if (package == null)
                throw new ArgumentNullException("package");

            LogisticsWarehouse theWarehouse = null; // default: not in a warehouse

            if (!PackageInWarehouse(package))
                return theWarehouse;

            theWarehouse = package.Warehouse; // should be set when package is in warehouse.

            /*
            var plan = PackageLatestPlan(package);
            if (plan != null && !package.WarehouseId.HasValue)
            {
                // case when an in-warehouse package has just been added to a plan.
                // This plan's warehouse must be where the package is in right now.
                theWarehouse = plan.Warehouse;
            }
            else
            {
                // all other cases, rely on what the package.Warehouse is assigned to.
                // (In all these cases, package.Warehouse must be assigned!!)
                theWarehouse = package.Warehouse;
            }
            */

            return theWarehouse;
        }

        public LogisticsLocation originLocationRouteInfo(LogisticsPackage package, LogisticsPlan plan)
        {
            if (package == null)
                throw new ArgumentNullException("package");

            if (plan == null)
                throw new ArgumentNullException("plan");

            LogisticsLocation origin = null;

            if ((package.Status == ShippingStatus.InWarehouse) ||
                (package.Status == ShippingStatus.InWarehouseFailedDelivery))
            {
                if (package.PickUpLocation.PlanId.HasValue &&
                    (package.PickUpLocation.PlanId.Value == plan.Id))
                {
                    origin = package.PickUpLocation;
                }
            }
            else if ((package.Status == ShippingStatus.ReadyForPickUp) ||
                     (package.Status == ShippingStatus.FailedPickUp))
            {
                origin = package.PickUpLocation;
            }
            else if (package.Status == ShippingStatus.ReturningWarehouse)
            {
                // status is returning warehouse but package could be still from the same warehouse
                // So needs to check if the pickup location is in the same plan
                if (package.PickUpLocation.PlanId.HasValue &&
                    package.PickUpLocation.PlanId.Value == plan.Id)
                {
                    origin = package.PickUpLocation;
                }
            }
            else if ((package.Status == ShippingStatus.Delivering) ||
                     (package.Status == ShippingStatus.Delivered) ||
                     (package.Status == ShippingStatus.ReturningWarehouseFailedDelivery))
            {
                if (package.PickUpLocation.PlanId.HasValue &&
                    package.DeliveryLocation.PlanId.HasValue &&
                    (package.PickUpLocation.PlanId.Value == package.DeliveryLocation.PlanId.Value))
                {
                    origin = package.PickUpLocation;
                }
            }
            else if ((package.Status == ShippingStatus.Cancelled) ||
                     (package.Status == ShippingStatus.ShippingNotRequired) ||
                     (package.Status == ShippingStatus.NotYetShipped) ||
                     (package.Status == ShippingStatus.PartiallyShipped) ||
                     (package.Status == ShippingStatus.Shipped) ||
                     (package.Status == ShippingStatus.NotYetReady))
            {
                // This case is supposed to catch the rest of the enum cases.
                // Some may be pertinent to logistics (e.g. Cancelled).
                // Others may not be useful to logistics.
                // Without this, some planning page will fail.

                origin = package.PickUpLocation;
            }
            else
                throw new InvalidOperationException("Package is in an unexpected shipping status state = " + package.Status.ToString());

            return origin;
        }

        /// <summary>
        /// To check whether a set of distinct locations can be used for a valid plan (i.e. no mixed within- and cross-district)
        /// </summary>
        /// <param name="locationIds">A list of distinct Location Ids (positive is valid Id, negative means warehouse (rather than location) Id</param>
        /// <returns>bool. True if valid list of Ids or empty list of Ids.</returns>
        public bool LocationsValidForPlan(IList<int> locationIds)
        {
            if (locationIds == null || !locationIds.Any(id => id != 0))
            {
                return true;
            }

            var nonWarehouseLocIds = locationIds
                .Where(id => id > 0);

            // TODO: refactoring
            // Warehouse IDs are negative and store as locations IDs.
            // See workaround in method originLocationRouteInfo
            // locId = -warehouse.Id; 
            // since warehouse is not a LogisticsLocation, give it a locationId as a negative warehouse.Id
            // Thus, any locId > 0 is "real" locationIds, while locId < 0 is a warehouseId

            var warehouseIds = locationIds
                .Where(id => id < 0)
                .Select(id => -id);

            int? firstDistrictId = null;

            if (nonWarehouseLocIds.Any())
            {
                firstDistrictId = GetLogisticsLocationById(nonWarehouseLocIds.First()).DistrictId;
            }
            else if (warehouseIds.Any())
            {
                firstDistrictId = GetWarehouseById(warehouseIds.First()).DistrictId;
            }

            if (!firstDistrictId.HasValue)
            {
                throw new NopException("The location doesn't has an associated district. (ID: {0})", nonWarehouseLocIds.First());
            }

            bool areLocsInSameDistrict;
            if (!nonWarehouseLocIds.Any())
            {
                areLocsInSameDistrict = true;
            }
            else
            {
                areLocsInSameDistrict = _locationRepository.Table
                    .Where(l => nonWarehouseLocIds.Contains(l.Id))
                    .All(l => l.DistrictId == firstDistrictId);
            }

            if (!areLocsInSameDistrict)
            {
                return false;
            }

            bool areWarehousesInSameRegion;
            if (!warehouseIds.Any())
            {
                areWarehousesInSameRegion = true;
            }
            else
            {
                var district = GetDistrictById(firstDistrictId.Value);

                areWarehousesInSameRegion = _warehouseRepository.Table
                    .Where(w => warehouseIds.Contains(w.Id))
                    .All(w => w.District.RegionId == district.RegionId);
            }

            return (areLocsInSameDistrict && areWarehousesInSameRegion);
        }

        #endregion

        protected virtual string GenerateTrackingNumber(string zoneName, string postalCode, int applicationId, int orderId, int packageId)
        {
            // {Reserve1 2#} {Ver = 01} {Zone #2} {PostalCode = 9} {ApplicationCustomerId 4#} {OrderId 6#} {PackageId 6#}
            // {Zone 2#} {OrderId 6#} {PackageId 6#}
            StringBuilder sbTrackingNumber = new StringBuilder();

            // Reserved
            //sbTrackingNumber.Append("8801");

            // Zone
            if (zoneName == null)
            {
                throw new ArgumentException("zoneName");
            }
            sbTrackingNumber.Append(zoneName);
            sbTrackingNumber.Append('-');

            // PostalCode
            //const int postalCodeLen = 6;
            //if (postalCode == null || postalCode.Length != postalCodeLen)
            //{
            //    throw new ArgumentException(String.Format("Postal code is invalid: {0}", postalCode));
            //}

            //var pc = postalCode.ToLower().ToCharArray(0, 6);
            //for (int i = 0; i < postalCodeLen; i++)
            //{
            //    if (Char.IsLetter(pc[i]))
            //    {
            //        pc[i] = (char)((pc[i] - 'a') % 10 + '0');
            //    }
            //}
            //sbTrackingNumber.Append(pc);

            // application customer id
            //sbTrackingNumber.Append((applicationId % 100000).ToString("00000"));

            // Order
            sbTrackingNumber.Append((orderId % 1000000).ToString("000000"));

            // Package
            sbTrackingNumber.Append((packageId % 1000000).ToString("000000"));

            return sbTrackingNumber.ToString();
        }

        #region Pending Ready

        public virtual void CheckReadyPackage()
        {
            var query = from p in _packageRepository.Table
                        where (p.Status == ShippingStatus.NotYetReady && p.ReadyOnUtc.HasValue)
                        select p;

            query.ToList()
                .Where(p => p.ReadyOnUtc.Value >= DateTime.UtcNow && p.Status == ShippingStatus.NotYetReady)
                .ToList()
                .ForEach(p => MarkPackageAsReady(p, _workContext.CurrentCustomer.Id));
        }

        public virtual void MarkPackageAsReady(LogisticsPackage package, int userId)
        {
            if (package.Status == ShippingStatus.ReadyForPickUp)
            {
                return;
            }

            package.Status = ShippingStatus.ReadyForPickUp;
            var shipment = package.Shipment;
            if (shipment == null)
            {
                return;
            }

            var order = shipment.Order;
            if (order == null || order.OrderKind != OrderKind.Express || order.ShippingStatus == ShippingStatus.ReadyForPickUp)
            {
                return;
            }

            bool hasNotReady = false;
            foreach (var s in order.Shipments)
            {
                if (s.Id != shipment.Id)
                {
                    var p = GetPackageByShipmentId(s.Id);
                    if (p != null && p.Status == ShippingStatus.NotYetReady)
                    {
                        hasNotReady = true;
                        break;
                    }
                }
            }

            if (!hasNotReady)
            {
                order.ShippingStatus = ShippingStatus.ReadyForPickUp;
                _orderService.UpdateOrder(order);

                package.Shipment.Order.OrderNotes.Add(new OrderNote()
                {
                    Note = string.Format("\"Order Status Update \" Status is changed to ReadyForPickup."),
                    DisplayToCustomer = false,
                    CreatedOnUtc = DateTime.UtcNow
                });
            }

            _packageRepository.Update(package);

            CreatePackageNote(package.Id, PackageNoteType.SystemEvent, "Package Updated", userId, true, ShippingStatus.ReadyForPickUp, package.PickUpLocation.Address);

            //TODO: Create message template and workflow.
            //var order = p.Shipment.Order;
            //int orderPlacedStoreOwnerNotificationQueuedEmailId = _workflowMessageService.SendLogisticsOrderPlacedManagerNotification(order, _workContext.WorkingLanguage.Id);
            //if (orderPlacedStoreOwnerNotificationQueuedEmailId > 0)
            //{
            //    order.OrderNotes.Add(new OrderNote()
            //    {
            //        Note = string.Format("\"Order Ready For Pick Update \" email (to store owner) has been queued. Queued email identifier: {0}.", orderPlacedStoreOwnerNotificationQueuedEmailId),
            //        DisplayToCustomer = false,
            //        CreatedOnUtc = DateTime.UtcNow
            //    });

            //    _orderService.UpdateOrder(order);
            //}
        }

        #endregion

        #region Zone & Region

        // Zone
        public void UpdateZone(Zone zone)
        {
            _zoneRepository.Update(zone);
        }

        public void InsertZone(Zone zone)
        {
            _zoneRepository.Insert(zone);
        }

        public void DeleteZoneByName(string zoneName)
        {
            var zone = GetZoneByName(zoneName);
            _zoneRepository.Delete(zone);
        }

        public Zone GetZoneByName(string zoneName)
        {
            var table = _zoneRepository.Table;
            return table.FirstOrDefault(z => z.Name == zoneName);
        }

        public Zone GetZoneByZipPostalCode(string zipPostalCode, string countryCode)
        {
            if (String.IsNullOrWhiteSpace(zipPostalCode))
            {
                return null;
            }

            var cc = countryCode.ToUpperInvariant();

            string pc;
            switch (cc)
            {
                case "CA":
                    if (zipPostalCode.Length < 6)
                    {
                        return null;
                    }
                    pc = zipPostalCode.Trim().ToUpper().Substring(0, 3);
                    break;
                default:
                    //throw new NopException("The country code ({0}) is invalid.", countryCode);
                    return null;
            }

            Zone zone = null;
            try
            {
                zone = GetAllZones(countryCode).SingleOrDefault(z => z.GetZipPostalCodeArray().Contains(pc));
            }
            catch (Exception e)
            {
                throw new NopException("The zone matching postal code ({0}) cannot be retrieved. Exception: {1}", zipPostalCode, e);
            }
            return zone;
        }

        public IList<Zone> GetAllZones(string countryCode)
        {
            var cc = countryCode.ToUpperInvariant();

            string key = string.Format(LOGISTICSZONES_ALLZONES_BY_COUNTRYCODE_KEY, cc);
            return _cacheManager.Get(key, () =>
                    _zoneRepository.Table
                        .Where(z => z.District.CountryCode == cc)
                        .ToList()
                );
        }

        // Region
        public IList<Region> GetAllRegions()
        {
            return _regionRepository.Table.ToList();
        }

        public Region GetRegionById(int id)
        {
            return _regionRepository.GetById(id);
        }

        public void InsertRegion(Region region)
        {
            _regionRepository.GetById(region);
        }

        public void UpdateRegion(Region region)
        {
            _regionRepository.Update(region);
        }

        public void DeleteRegion(Region region)
        {
            _regionRepository.Delete(region);
        }

        public string GetCountryCodeByZipPostalCode(string zipPostalCode)
        {
            if (Regex.IsMatch(zipPostalCode, @"^ *[ABCEGHJKLMNPRSTVXYabceghjklmnprstvxy]{1}\d{1}[A-Za-z]{1} *\d{1}[A-Za-z]{1}\d{1} *$"))
            {
                return "CA";
            }
            return null;
        }

        public Region GetRegionByZipPostalCode(string zipPostalCode)
        {
            if (String.IsNullOrWhiteSpace(zipPostalCode))
            {
                return null;
            }

            var cc = GetCountryCodeByZipPostalCode(zipPostalCode);
            if (cc == null)
            {
                return null;
            }

            var zone = GetZoneByZipPostalCode(zipPostalCode, cc);
            if (zone == null)
            {
                return null;
            }

            return zone.District.Region;
        }

        public District GetDistrictByZipPostalCode(string zipPostalCode)
        {
            if (String.IsNullOrWhiteSpace(zipPostalCode))
            {
                return null;
            }

            var cc = GetCountryCodeByZipPostalCode(zipPostalCode);
            if (cc == null)
            {
                return null;
            }

            var zone = GetZoneByZipPostalCode(zipPostalCode, cc);
            if (zone == null)
            {
                return null;
            }

            return zone.District;
        }

        public bool IsZipPostalCodeInRegion(Region region, string zipPostalCode)
        {
            if (String.IsNullOrWhiteSpace(zipPostalCode))
            {
                return false;
            }

            var cc = GetCountryCodeByZipPostalCode(zipPostalCode);
            if (cc == null)
            {
                return false;
            }

            var zone = GetZoneByZipPostalCode(zipPostalCode, cc);
            if (zone == null)
            {
                return false;
            }
            return zone.District.RegionId == region.Id;
        }

        public IList<StateProvince> GetSupportedStateProvinces(Region region)
        {
            return region.Districts.Select(d => d.StateProvince).Distinct(new StateProvinceComparer()).ToList();
        }

        // District
        public IList<District> GetAllDistricts()
        {
            return _districtRepository.Table.OrderBy(d => d.DisplayOrder).ToList();
        }

        public IList<District> GetAllDistrictsInRegion(Region region)
        {
            if (region == null)
                throw new ArgumentNullException("region");

            var query = from d in _districtRepository.Table
                        where d.RegionId == region.Id
                        select d;

            return query.ToList();
        }

        public District GetDistrictById(int id)
        {
            return _districtRepository.GetById(id);
        }

        public void InsertDistrict(District district)
        {
            _districtRepository.Insert(district);
        }

        public void UpdateDistrict(District district)
        {
            _districtRepository.Update(district);
        }

        public void DeleteDistrict(District district)
        {
            _districtRepository.Delete(district);
        }

        // Zone group
        public IList<ZoneGroup> GetAllZoneGroups(District district)
        {
            return _zoneGroupRepository.Table.ToList();
        }

        public ZoneGroup GetZoneGroupById(int id)
        {
            return _zoneGroupRepository.GetById(id);
        }

        public void InsertZoneGroup(ZoneGroup zoneGroup)
        {
            _zoneGroupRepository.Insert(zoneGroup);
        }

        public void UpdateZoneGroup(ZoneGroup zoneGroup)
        {
            _zoneGroupRepository.Update(zoneGroup);
        }

        public void DeleteZoneGroup(ZoneGroup zoneGroup)
        {
            _zoneGroupRepository.Delete(zoneGroup);
        }

        class StateProvinceComparer : IEqualityComparer<StateProvince>
        {
            public bool Equals(StateProvince x, StateProvince y)
            {
                if (x != null && y != null)
                {
                    return x.Id == y.Id;
                }

                return (x == null && y == null);
            }

            public int GetHashCode(StateProvince obj)
            {
                return obj.Id;
            }
        }


        #endregion

        #region PlanNote

        public void CreatePlanNote(PlanNoteStatus planNoteStatus, int planId, PlanStatus planStatus, int customerId, bool validForCommission, bool displayToCourier,
                                   int? packageId, ShippingStatus? packageStatus, int? locationId, Address address)
        {
            // assumes planId is valid, so no check here.
            // Same as customerId and packageId.
            // If not valid, there is a chance a transaction would have happened and, if this note
            // creation failed, then a note would not have been created for that transaction.

            var planNoteStatusStr = PlanNoteCommon.PlanNoteStatusString[(int)planNoteStatus];

            var noteString = String.Join("|", new string[] { planNoteStatusStr, ((int)planStatus).ToString(), customerId.ToString() });

            // Only does the code block if it is about a package (i.e. packageId has value).
            if (packageId.HasValue)
            {
                noteString = String.Join("|", new string[] { noteString, 
                                                             packageId.Value.ToString(),
                                                             packageStatus.HasValue ? ((int)packageStatus.Value).ToString() : String.Empty,
                                                             address != null ? address.Id.ToString() : String.Empty });
            }

            var planNote = new PlanNote()
            {
                PlanId = planId,
                LocationId = locationId,
                Note = noteString,
                ValidForCommission = validForCommission,
                DisplayToCourier = displayToCourier,
                CreatedOnUtc = DateTime.UtcNow,
            };

            CreatePlanNote(planNote);
        }

        public void CreatePlanNote(PlanNote planNote)
        {
            if (planNote == null)
                throw new ArgumentNullException("planNote");

            _planNoteRepository.Insert(planNote);
        }

        public void UpdatePlanNote(PlanNote planNote)
        {
            if (planNote == null)
                throw new ArgumentNullException("planNote");

            _planNoteRepository.Update(planNote);
        }

        public PlanNote GetPlanNoteById(int planNoteId)
        {
            return _planNoteRepository.GetById(planNoteId);
        }

        public ICollection<PlanNote> GetPlanNotesByPlanId(int planId)
        {
            var query = from pn in _planNoteRepository.Table
                        where pn.PlanId == planId
                        select pn;

            return query.ToList();
        }

        public ICollection<PlanNote> GetPlanNotesByPackageId(int packageId, int? planId)
        {
            var package = GetPackageById(packageId);
            if (package == null)
                return null;

            var pickupLoc = package.PickUpLocation;
            var deliveryLoc = package.DeliveryLocation;

            var query = from pn in _planNoteRepository.Table
                        where pn.LocationId.HasValue && (pn.LocationId.Value == pickupLoc.Id || pn.LocationId.Value == deliveryLoc.Id)
                        select pn;

            // if provided, narrow the criteria to only plan notes associated with a plan.
            if (planId.HasValue && planId.Value > 0)
            {
                query = query.Where(pn => pn.PlanId == planId.Value);
            }

            query = query.OrderByDescending(pn => pn.CreatedOnUtc);

            return query.ToList();
        }

        public ICollection<PlanNote> GetPlanNotesForComissionByPlanId(int planId)
        {
            // For commission purposes, a planNote record must have a locationId associated to it.
            // Also, only cares about those planNotes that have valid for commission = true
            // Only counts a locationId's "True valid for commission" once only!
            // For the purpose of counting/displaying, select the last record for that locationId.
            var query = (from pn in _planNoteRepository.Table
                         where pn.PlanId == planId && pn.LocationId.HasValue &&
                         pn.ValidForCommission == true
                         select pn)
                        .GroupBy(g => g.LocationId.Value);
            //s.Select(s => s.OrderBy(pn => pn.CreatedOnUtc));
            return query.ToList().Select(ls =>
            {
                /*
                if (ls.Count() != 1)
                {
                    throw new NopException("Location {0}, () has multiple commision records.", ls.First().LocationId.Value);
                }
                */
                return ls.OrderBy(pn => pn.CreatedOnUtc).Last();
            }).ToList();
        }

        public ICollection<LogisticsPackage> GetPackagesFromPlanNotesByPlanId(int planId)
        {
            if (planId <= 0)
                throw new Exception("Invalid planId = " + planId.ToString());

            var query = (from pn in _planNoteRepository.Table
                         where pn.PlanId == planId && pn.LocationId.HasValue
                         select pn.LocationId).Distinct();

            var query1 = (from loc in _locationRepository.Table
                          join pkg in _packageRepository.Table on loc.PackageId equals pkg.Id
                          where query.Contains(loc.Id)
                          select pkg).Distinct();

            query1.OrderBy(pkg => pkg.Id);

            return query1.ToList();
        }

        public virtual IPagedList<PlanNote> SearchPlanNotes(DateTime? startDate,
                                                            DateTime? endDate,
                                                            int? driverId,
                                                            bool aboutPackagesOnly,
                                                            int pageIndex, int pageSize)
        {
            var query = _planNoteRepository.Table;

            if (startDate.HasValue)
                query = from pn in query
                        where pn.CreatedOnUtc >= startDate.Value
                        select pn;

            if (endDate.HasValue)
                query = from pn in query
                        where pn.CreatedOnUtc <= endDate.Value
                        select pn;

            if (aboutPackagesOnly)
                query = from pn in query
                        where pn.LocationId.HasValue
                        select pn;

            if (driverId.HasValue && driverId.Value > 0) // 0 is invalid DB table's key value
            {
                query = from pn in query
                        join pl in _planRepository.Table on pn.PlanId equals pl.Id
                        where pl.CourierId == driverId.Value
                        select pn;
            }

            var results = new PagedList<PlanNote>(query.ToList(), pageIndex, pageSize);
            return results;
        }


        #endregion

        #region PackageNote

        public void CreatePackageStatusChangeNote(LogisticsPackage package, bool displayToCustomer, Address address)
        {
            var noteContent = GetPackageNoteContentByStatus(package.Status, _workContext.WorkingLanguage.Id);

            var packageNote = new PackageNote()
            {
                PackageId = package.Id,
                CustomerId = _workContext.CurrentCustomer.Id,
                Note = noteContent,
                Type = PackageNoteType.SystemEvent,
                DisplayToCustomer = displayToCustomer,
                CreatedOnUtc = DateTime.UtcNow,
                ShippingStatus = package.Status,
                AddressId = address != null ? (int?)address.Id : null,
            };

            CreatePackageNote(packageNote);
        }

        public void CreatePackageNote(int packageId, PackageNoteType noteType, string noteContent, int userId, bool displayToCustomer,
                                      ShippingStatus? shippingStatus, Address address)
        {
            if (String.IsNullOrWhiteSpace(noteContent))
                throw new Exception(String.Format("Package Note content for package ID {0} cannot be empty", packageId));

            var pkg = GetPackageById(packageId);
            if (pkg == null)
                throw new Exception(String.Format("Package not found for package ID {0}", packageId));

            var user = _customerService.GetCustomerById(userId);
            if (user == null)
                throw new Exception(String.Format("User not found for user ID {0}", userId));

            if (shippingStatus.HasValue)
            {
                if (address == null && shippingStatus != ShippingStatus.Cancelled)
                {
                    throw new Exception(String.Format("Address cannot be null when Package Note is to contain shipping status"));
                }
            }

            var packageNote = new PackageNote()
            {
                PackageId = packageId,
                CustomerId = userId,
                Note = noteContent,
                Type = noteType,
                DisplayToCustomer = displayToCustomer,
                CreatedOnUtc = DateTime.UtcNow,
                ShippingStatus = shippingStatus,
                AddressId = address != null ? (int?)address.Id : null,
            };

            CreatePackageNote(packageNote);
        }

        public void CreatePackageNote(PackageNote packageNote)
        {
            if (packageNote == null)
                throw new ArgumentNullException("packageNote");

            _packageNoteRepository.Insert(packageNote);
        }

        public void UpdatePackageNote(PackageNote packageNote)
        {
            if (packageNote == null)
                throw new ArgumentNullException("packageNote");

            _packageNoteRepository.Update(packageNote);
        }

        public PackageNote GetPackageNoteById(int noteId)
        {
            return _packageNoteRepository.GetById(noteId);
        }

        public ICollection<PackageNote> GetPackageNotesByPackageId(int packageId,
                                                                   int? numberOfNotes = null,
                                                                   PackageNoteType[] noteTypes = null,
                                                                   bool? displayToCustomer = null,
                                                                   int? userId = null,
                                                                   bool shippingStatusOnly = false)
        {
            var query = _packageNoteRepository.Table
                .Where(pn => pn.PackageId == packageId);

            if (shippingStatusOnly)
            {
                query = query.Where(pn => pn.ShippingStatusId.HasValue);
            }

            if (noteTypes != null && noteTypes.Length > 0)
            {
                query = query.Where(pn => noteTypes.Contains((PackageNoteType)pn.TypeId));
            }

            if (displayToCustomer.HasValue)
            {
                query = query.Where(pn => pn.DisplayToCustomer == displayToCustomer.Value);
            }

            if (userId.HasValue)
            {
                query = query.Where(pn => pn.CustomerId == userId.Value);
            }

            if (numberOfNotes.HasValue && numberOfNotes.Value > 0)
            {
                return query.OrderByDescending(pn => pn.CreatedOnUtc).Take(numberOfNotes.Value).ToList();
            }

            return query.OrderByDescending(pn => pn.CreatedOnUtc).ToList();
        }

        /// <summary>
        /// Adapting the package notes or the plan notes to shipment status events by package
        /// </summary>
        /// <param name="package">package</param>
        /// <param name="customerOnly">populate the display-to-customer events only</param>
        /// <returns>Shipment status events</returns>
        public virtual IList<ShipmentStatusEvent> GetPackageEvents(LogisticsPackage package, bool customerOnly = true)
        {
            if (package == null)
            {
                throw new ArgumentNullException("package");
            }

            var result = new List<ShipmentStatusEvent>();

            if (package.PackageNoteVersion == 0)
            {
                //pull out events/history from the plan notes.
                var planNotes = GetPlanNotesByPackageId(package.Id, null);

                foreach (var pn in planNotes)
                {
                    PlanNoteStatus pnAction;
                    PlanStatus pnPlanStatus;
                    int pnCustomerId = -1;
                    int? pnPackageId = null;
                    ShippingStatus? pnPackageStatus = null;
                    int? addressId = null;

                    pn.ExtractPlanNoteStringContent(out pnAction, out pnPlanStatus, out pnCustomerId,
                                                    out pnPackageId, out pnPackageStatus, out addressId);

                    if ((pnAction != PlanNoteStatus.UpdatePackage) && (pnAction != PlanNoteStatus.UndoPackagePickup) &&
                        (pnAction != PlanNoteStatus.UndoPackageDelivery))
                    {
                        // skip to next one
                        continue;
                    }

                    var location = GetLogisticsLocationById(pn.LocationId.Value);

                    Address address = null;
                    if (addressId.HasValue)
                    {
                        address = _addressSerivce.GetAddressById(addressId.Value);
                    }

                    var userDateTime = _dateTimeHelper.ConvertToUserTime(pn.CreatedOnUtc, DateTimeKind.Utc);
                    var shpStatusEvent = new ShipmentStatusEvent()
                    {
                        CountryCode = location.Address.Country.ThreeLetterIsoCode,
                        DateTime = userDateTime,
                        EventName = pnPackageStatus.Value.GetLocalizedEnum(_localizationService, _workContext),

                        // if address == null, continue to use location.Address even though it's not exactly right.
                        // (That is, better than completely empty, I think).
                        Location = address != null ? address.FormattedAddress() : location.Address.FormattedAddress(),
                    };

                    result.Add(shpStatusEvent);
                }
            }
            else if (package.PackageNoteVersion == 1)
            {
                //pull out events from the package notes.
                var pkgNoteTypes = new PackageNoteType[] { PackageNoteType.SystemEvent, PackageNoteType.AdminNote };
                var pkgNotes = GetPackageNotesByPackageId(package.Id, null, pkgNoteTypes, true, null, true);

                foreach (var pn in pkgNotes)
                {
                    //TODO: Convert to local time
                    var userDateTime = _dateTimeHelper.ConvertToUserTime(pn.CreatedOnUtc, DateTimeKind.Utc);

                    var shippingStatus = pn.ShippingStatus.HasValue ? pn.ShippingStatus.Value.GetLocalizedEnum(_localizationService, _workContext) : null;

                    if (shippingStatus == null)
                        shippingStatus = pn.Note;

                    var location = "";
                    if (pn.AddressId.HasValue)
                    {
                        var address = _addressSerivce.GetAddressById(pn.AddressId.Value);
                        if (address != null)
                        {
                            location = address.City;
                            if (!String.IsNullOrEmpty(address.StateProvince.Name))
                            {
                                if (!String.IsNullOrEmpty(address.StateProvince.Abbreviation))
                                    location += ", " + address.StateProvince.Abbreviation;
                                else
                                    location += ", " + address.StateProvince.Name;
                            }
                        }
                    }

                    var shpStatusEvent = new ShipmentStatusEvent()
                    {
                        CountryCode = "CA", // TODO: Remove the hard code.
                        DateTime = userDateTime,
                        EventName = pn.Note,
                        Location = location,
                    };

                    result.Add(shpStatusEvent);
                }
            }
            else
            {
                throw new NopException("The package note version {0} is not supported.", package.PackageNoteVersion);
            }

            return result;
        }

        /// <summary>
        /// Gets the count of failed delivery events
        /// </summary>
        /// <param name="package"></param>
        /// <returns></returns>
        public virtual int GetFailedDeliveryEventCount(LogisticsPackage package)
        {
            if (!package.DeliveryAttemptCount.HasValue)
            {
                var query = from pn in _packageNoteRepository.Table
                            where package.Id == pn.PackageId &&
                                pn.DisplayToCustomer &&
                                pn.ShippingStatusId == (int)ShippingStatus.ReturningWarehouseFailedDelivery
                            select pn;
                package.DeliveryAttemptCount = query.Count();
                UpdatePackage(package, false);
            }
            return package.DeliveryAttemptCount.Value;
        }

        #endregion

        #region Address

        /// <summary>
        /// Gets all addresses which types are inconclusive.
        /// </summary>
        /// <param name="districtId">district</param>
        /// <param name="pageIndex">page index</param>
        /// <param name="pageSize">page size</param>
        /// <returns></returns>
        public virtual PagedList<Address> GetAllTypeInconclusiveAddresses(int? districtId, int pageIndex, int pageSize)
        {
            var query = from a in _addressRepository.Table
                        where a.TypeId.HasValue && a.VerifiedTypeId.HasValue && a.TypeId != a.VerifiedTypeId
                        select a;

            query = query.OrderByDescending(a => a.CreatedOnUtc);

            if (districtId.HasValue && districtId.Value != 0)
            {
                var res = query.ToList().Where(a =>
                    {
                        var d = GetDistrictByZipPostalCode(a.ZipPostalCode);
                        return d == null || d.Id == districtId.Value;
                    }).ToList();
                return new PagedList<Address>(res, pageIndex, pageSize);
            }

            return new PagedList<Address>(query, pageIndex, pageSize);
        }

        #endregion

        #region Package note template

        /// <summary>
        /// Gets the template by identifier
        /// </summary>
        /// <param name="packageNoteTemplateId"></param>
        /// <returns></returns>
        public virtual PackageNoteTemplate GetPackageNoteTemplateById(int packageNoteTemplateId)
        {
            if (packageNoteTemplateId == 0)
                return null;

            return _packageNoteTemplateRepository.GetById(packageNoteTemplateId);
        }

        /// <summary>
        /// Updates the template
        /// </summary>
        /// <param name="packageNoteTemplate"></param>
        public virtual void UpdatePackageNoteTemplate(PackageNoteTemplate packageNoteTemplate)
        {
            if (packageNoteTemplate == null)
                throw new ArgumentNullException("packageNoteTemplate");

            //update
            _packageNoteTemplateRepository.Update(packageNoteTemplate);
        }

        /// <summary>
        /// Inserts a template
        /// </summary>
        /// <param name="packageNoteTemplate"></param>
        public virtual void InsertPackageNoteTemplate(PackageNoteTemplate packageNoteTemplate)
        {
            if (packageNoteTemplate == null)
                throw new ArgumentNullException("packageNoteTemplate");

            //insert
            _packageNoteTemplateRepository.Insert(packageNoteTemplate);
        }

        /// <summary>
        /// Deletes a template
        /// </summary>
        /// <param name="packageNoteTemplate"></param>
        public virtual void DeletePackageNoteTemplate(PackageNoteTemplate packageNoteTemplate)
        {
            if (packageNoteTemplate == null)
                throw new ArgumentNullException("packageNoteTemplate");

            _packageNoteTemplateRepository.Delete(packageNoteTemplate);
        }

        /// <summary>
        /// Gets all templates by the reference identifier
        /// </summary>
        /// <param name="referenceId"></param>
        /// <param name="publishedOnly"></param>
        /// <returns></returns>
        public virtual List<PackageNoteTemplate> GetPackageNoteTemplates(int? referenceId = null, bool publishedOnly = true)
        {
            var query = from r in _packageNoteTemplateRepository.Table
                        orderby r.DisplayOrder
                        where (!publishedOnly || r.Published)
                        select r;
            if (referenceId.HasValue)
                query = query.Where(r => r.ReferenceId == referenceId.Value);
            return query.ToList();
        }

        /// <summary>
        /// Gets all templates by the status
        /// </summary>
        /// <param name="packageStatus"></param>
        /// <returns></returns>
        public virtual List<PackageNoteTemplate> GetPackageNoteTemplatesByStatus(ShippingStatus packageStatus)
        {
            return GetPackageNoteTemplates((int)packageStatus, true);
        }

        /// <summary>
        /// Gets the package note content by standard template or loaded template
        /// </summary>
        /// <param name="template"></param>
        /// <param name="languageId"></param>
        /// <returns></returns>
        public virtual string GetPackageNoteContentByTemplate(PackageNoteTemplate template, int languageId)
        {
            if (template.Id == 0)
                // Standard template
                return GetPackageNoteContentByName(template.SystemName, languageId) ?? template.Content;

            return template.GetLocalized(x => x.Content, languageId);
        }

        /// <summary>
        /// Gets the package note content by template system name
        /// </summary>
        /// <param name="systemName"></param>
        /// <param name="languageId"></param>
        /// <returns></returns>
        public virtual string GetPackageNoteContentByName(string systemName, int languageId)
        {
            var query = from r in _packageNoteTemplateRepository.Table
                        orderby r.DisplayOrder
                        where r.Published && r.SystemName == systemName
                        select r;

            var note = query.FirstOrDefault();
            return note == null ? null : note.GetLocalized(x => x.Content, languageId);
        }

        /// <summary>
        /// Gets the package note content by status
        /// </summary>
        /// <param name="status"></param>
        /// <param name="languageId"></param>
        /// <returns></returns>
        public virtual string GetPackageNoteContentByStatus(ShippingStatus status, int languageId)
        {
            var query = from r in _packageNoteTemplateRepository.Table
                        orderby r.DisplayOrder
                        where r.Published && r.ReferenceId == (int)status
                        select r;

            var note = query.FirstOrDefault();
            if (note != null)
                return note.GetLocalized(x => x.Content, languageId);

            return GetPackageNoteContentByTemplate(PackageNoteStandardTemplates.StatusChanged, languageId);
        }



        #endregion
        public virtual List<string> GetCustomerRole(List<int> MappingRoleId)
        {
            var getRole = from ct in _customerRole.Table
                          where MappingRoleId.Contains(ct.Id)
                          select ct.SystemName;
            return getRole.ToList<string>();
        }
    }
}
